Get your Power BI usage statistics in your email every day

Having a clear understanding of how your content is being used can help you prove your impact and prioritize your efforts. Some of your reports may be used every day by a large part of your company, while others may not be used for a long time. Feedback of this kind is invaluable in guiding your work efforts. This article shows how to create a flow in Power Automate to get the Power BI usage report in your email daily. Below are the main items I used to create a flow: - Usage Metrics Report - Power BI Dataset - Run a query against a dataset - Power Automate Action

- Create HTML table & Compose - Power Automate Action - Send an email (V2) - Power Automate Action

Flow Structure:

In this article, I used the new Power BI action in the Power Automate labelled Run a query against a dataset announced on 25-May-2022. With this action, we don't need to use the HTTP action in our Flows to access the ExecuteQuery REST API, which reduces many other steps. If you would like more information about the Power BI REST API using Power Automate, check out my previous Blog Post: Call Power BI Rest APIs with Power Automate.

To create this flow, click on the new flow, and select the Scheduled Cloud flow from the list. In the new window, add a name for your flow, select the starting date, and configure it to repeat every day, then click on the CREATE button. Now you have a flow with one trigger; click on the Recurrence → Edit → Show advanced options, then select the hour you want the flow to run. I selected 10 AM.

Add the first action, click on the New step, and search "power bi query" To add the first action, click on the New step, search for "power bi query", and select Run a query against a dataset. Select your Workspace, then in the Dataset field, select the Usage Metrics Report, copy the following Dax expression and paste it into the Query Text Field:

DEFINE
VAR __AA =
    SUMMARIZE (
        FILTER ( 'Report views', 'Report views'[Date] = TODAY () - 1 ),
        'Report views'[ReportName],
        'Report views'[UserId],
        "Views", COUNTROWS ( 'Report views' )
    )
EVALUATE
    SELECTCOLUMNS (
        __AA,
        "Report Name", [ReportName],
        "User ID", [UserId],
        "No. Views", [Views]
    )

Next, add the "Create HTML Table" as the following action to this flow to create a table and use that in a daily email; The action has an input field called From; click on this field and select the "First table row", which is one of the outputs from the previous action.

The HTML Table action produces a table without any style items (Border, Color, Font, etc.). Since the table is the main output, I want to add some style items to make it easier to understand and more visually appealing. Styles can be added to the HTML table of this flow by adding the Compose action and using CSS codes.

• Add Compose Action to the Flow

• This action has one field, Copy and paste the following code to the Inputs field:

<style>
Table {
  font-family: Arial, Helvetica, sans-serif;
  background-color: #EEEEEE;
  border-collapse: collapse;
  width: 100%;
}
Table td, Table th {
  border: 1px solid #ddd;
  padding: 3px 3px;
}
Table th {
  font-size: 15px;
  font-weight: bold;
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>

• Add the "Create HTML Table" output to the end of this code.

The table is ready now, and it has the number of views based on Report Names and User IDs (Email Address) [the Query result from the first action], but I'd like to include another table in the daily email as a report views summary that has two fields: Report Name and the number of views. A parallel branch needs to be added between the trigger (First Item in the Flow) and the First action (The Second Item in the flow) for adding a new table to the output of this flow. Same actions (Run a query against a dataset → Create HTML Table → Compose) need to be used in the parallel branch with the different query.

Copy and paste the below query in the "Run a query against a dataset" action of the new branch:

DEFINE
VAR __AA =
    SUMMARIZE (
        FILTER ( 'Report views', 'Report views'[Date] = TODAY () - 1 ),
        'Report views'[ReportName],
        "Views", COUNTROWS ( 'Report views' )
    )
EVALUATE
    SELECTCOLUMNS (
        __AA,
        "Report Name", [ReportName],
        "No. Views", [Views]
    )
ORDER BY
     [No. Views] DESC

Now all report items (Two Tables) are ready to be sent; the last action in this flow is the "Send an email (V2)"; This action has 3 fields that need to be set as follows:

1. To: Enter the email addresses of the recipients of this report in this field

2. Subject: Add a subject for your email; I used Usage Report with a timestamp.

3. Body: You can add some text in this field with the output of the Composes actions (Two Tables) in order to to make the email and included information more readable and understandable.

Now your flow is ready, and you can test it. You will see the following output after clicking on the Test icon at the top right:

Notes:

• The Usage Metrics Report dataset contains usage data for the last 30 days. It can take up to 24 hours for new usage data to be imported. You can't trigger a manual refresh by using the Power BI user interface, so you can create another flow to update this dataset every day before running this flow.

• To specify the Report Date, you can add the Convert Time Zone action to the flow and use the output in the Email action as below: