Power BI Pixel – A new way to track usage on Power BI

Back in May 2019, I introduced the Power BI Pixel technique to track usage on Power BI. In today’s post, I will share the detailed implementation steps to help you build your own Power BI Pixel and track users and the filter selections they make in your Power BI report.

Note: If you care about IT security and data protection, make sure you carefully read this article and the security section at the end. While this technique is all about enabling Power BI report owners to drive more value from their reports through tracking of users and their data selections, the Power BI pixel can be exploited to leak corporate data if the Power BI reports are authored by untrusted sources.

Why do we need a Power BI Pixel?

First, let’s reiterate the rationale for using a Power BI Pixel. BI is all about measuring your business success. But what about BI on BI? Wouldn’t it be crucial for your organization and business to measure the success of your mission-critical BI reports? While the current out-of-the-box Power BI solution that is provided by Microsoft to track usage is easy to deploy, it is currently missing the ability to monitor the selections users make in their reports (e.g., clicking a slicer or a bar in chart).

Imagine that you could be able to know who clicks on filters or slice and dice any element in your reports.

Imagine that you could find out which values in your data were clicked or selected in slicers.

Imagine that you could build automated tests to actively validate which data is shown to users and confirm the quality of your reports and the correctness of the row-level security logic.

With the Power BI Pixel, you can achieve all of the above and in today’s post, you will learn how to implement it. Before we deep dive into the implementation, let’s start with the proof-of-concept that I published in May 2019.

The Template App Proof-of-Concept

Before you learn how to build your own Power BI Pixel you are welcome to test the demonstration that I published in May 2019. If you have permissions to install private template apps, you can install this template App to see how I track your email address and the cities you select in my demo report.

After you click the link, click Install. (Make sure you have the permissions by your tenant admin to install Template Apps on Power BI).

After a short load of the template app, you will see the following App installed on your Power BI Apps page.

Open the App and read the introduction. When you are ready, click the arrow button. Note, once you click the arrow button, I will get to see your email address. Your email address will not be used in any way beyond the purpose of demonstrating the Power BI Pixel.

On the next page, you can see a map and a bar chart. You can slice and dice the different cities. Each time you load this page or slice it, you will receive an email from me at support@datachant.com with the cities you selected.

Here is a screenshot of the email you will receive. Remember, each time you click on a city in the map or bar chart, or load the report page, you will receive another email with the selected cities and their corresponding amounts. If you don’t want to get these emails, delete the report, or find the hidden pixel and remove it.

You can also edit the report and try to find the hidden pixel. Did you find it It’s time to drill-down and learn how I implemented the Power BI Pixel.

Solution Overview

The Power BI Pixel consists of four elements, as shown in the diagram below:

• A Power BI Report with a hidden table and a special measure that is formatted as a Web Image.
• A Power Automate UI Flow.
• A Web Server that hosts the pixel image (You can use my own web server that hosts the Pixel image. No need to own a web server).
• A Share Point Online list to collect the user’s email and filtered data.

The solution is implemented in 7 steps:

1. The user loads the Power BI report with the Power BI Pixel, or slices and dices the already opened report.
2. A measure in a hidden table is re-evaluated whenever the user slices and dices the report page. Since the measure is formatted as a Web Image, the report generates a Web request to a Power Automate HTTP request trigger. Using SELECTEDVALUE, CONCATENATEX and USERNAME functions in DAX, the filtered data and user’s email are sent in the URL path.
3. Power Automate is triggered by the Web request from Power BI and sends a Web request to an external web server (DataChant.com) to retrieve the pixel image (You can probably implement the retrieval of the pixel in other ways. This was the easiest way for me).
4. Power Automate receives the pixel image.
5. Power Automate responds back to Power BI with a Web response that includes the pixel image.
6. The invisible table in the Power BI report is loaded with the returned pixel image.
7. Power Automate stores the user’s email and filtered data on a Share Point List (or on your preferred database or file).

The Power Automate Implementation

In this section, you will learn how to implement a basic Power Automate flow that is triggered when the Power BI Pixel is requested and implement Steps 3-6 above. In the next section, you will learn how to implement the invisible Power BI elements.

Note: To follow the next steps, you would need a Power Automate account that includes the Premium connectors.

Login to Power Automate here. Click Create and select Automated Flow.

Enter Power BI Pixel in the Flow Name text box, and click Skip.

Enter http request in the search box and wait until the results are loaded in the Triggers list below. Select When a HTTP request is received.

In the trigger pane, select GET in the method drop-down menu. Then, in the relativePath text box, enter UserData/{data}

This step will allow you to pass the user data from the Power BI report using a parameter UserData that will be implemented in the Power BI report in the next section.

Note: You can see that the URL is not generated yet. It will be generated after you save the flow. The URL will have the following format:

https://prod-88.westus.logic.azure.com/workflows/.../triggers/manual/paths/invoke/UserData/{data}?...

In the later stages of the flow, you will learn how to extract the data that will be provided instead of the placeholder {data} after the UserData/ part.

Let’s fetch the pixel image. To create the next action, click Next Step. In the Choose an action section, enter HTTP in the search box and select HTTP.

In the HTTP action, select GET as Method, and enter the URI https://datachant.com/img/PBIPixel.png

Note that the URI above is for a 1×1 pixel PNG file that I placed on my blog. You can download the pixel image here and host it on your own web server. For learning purposes, you can keep using my URI (I am not collecting any personal data when you use it).

Click Next Step. In the Choose an action section, enter Response in the search box and select Response as shown in the screenshot below (Note that this action was designed as the natural output of the HTTP Request trigger that we used earlier. For some strange reason, it was not named as HTTP Response, but only as Response).

It’s time to customize the response to the initial HTTP request as shown in the four steps in the screenshot below:

1. Enter 200 as the Status Code.
2. Enter Content-Type in the Enter key text box.
3. Enter image/png in the Enter value.
4. Click the Body text box, and in the Dynamic content tab, click Body (under the HTTP section).

Following the last step, the flow will return the pixel image to Power BI. Since the flow stores the HTTP request that triggered it, we can now move to the extraction of the user’s email and filtered data that will be embedded in the URL. We will start with the user’s email which will be embedded as the last string in a semicolon-delimited text instead of the placeholder {data} in the UserData/{data} part of the URL path. In this step, we will extract the email and store it in a flow variable.

Click Next Step. In the Choose an action section, enter initialize var in the search box and select Initialize Variable.

In the Initialize variable section, follow the 5 steps below as illustrated in the screenshot.

1. Enter Email in the Name text box.
2. Select String in Type drop-down menu.
3. Click the Value text box.
4. Move from the Dynamic content tab to the Expression tab.
5. Click the formula bar (fx) text box.

In the formula bar, enter the text below which represents the beginning of our expression:

last(split(

Note: The text above is not the complete expression. If you are a savvy Power Automate developer, you can skip the next steps and copy this full expression:

last(split(triggerOutputs()['relativePathParameters']['data'],';')) 

The expression above extracts the user email from the last semicolon-delimited string of {data} placeholder in the UserData/{data} URL path. However, since I find the navigation in Power Automate a bit elusive, you are welcome to skip the full expression above and see how I generated it in the steps below. The main challenge, in my opinion, was to figure out how to read the {data} placeholder and then manipulate it and extract from it the last semicolon-delimited text. The Dynamic tab was not very helpful to achieve this scenario.

Back to our simplified walkthrough… In the last step you added the beginning expression:

last(split(

The auto-complete feature will add two closing parentheses. Keep the cursor where it is (after the second opening parenthesis) and go to the Dynamic content tab to fetch the {data} part

Select data as shown in the screenshot below.

You can now see in the formula bar the following partial expression:

last(split(triggerOutputs()['relativePathParameters']['data'])) 

To complete the code, we will need to add the semicolon delimiter as the second argument for the split function, as shown in the screenshot below.

Here is the final formula:

last(split(triggerOutputs()['relativePathParameters']['data'],';')) 

You can now click the OK button below the formula bar.

Let’s quickly explain the code. The section triggerOutputs()[‘relativePathParameters’][‘data’] returns the data that was embedded in the URL in the {data} placeholder. We assume that the email is included as the last semicolon-delimited text, hence we apply the split by ‘;’ and apply last on the returned split list to retrieve the last split text.

Rename the Initialize variable step to Extract Email. It’s time to extract the filtered data from the {data} placeholder. The data will be stored in the first semicolon-delimited text.

Click Next Step. In the Choose an action section, enter initialize var in the search box and select Initialize Variable. Rename the step to Extract Data and follow the 6 steps below as shown in the screenshot.

1. Enter Data in the Name text box.
2. Select String in Type drop-down menu.
3. Click the Value text box.
4. Move from the Dynamic content tab to the Expression tab.
5. Click the formula bar (fx) text box.
6. In the formula bar, enter the expression below and then click OK under the formula bar.

 first(split(triggerOutputs()['relativePathParameters']['data'],';'))  

The flow is now ready to store the Email and Data variables in your preferred storage.

For example, from here you can create a new step with the Share Point connector and the Create Item action and drag and drop the Email and Data variables to the relevant columns in your Share Point List as illustrated in this screenshot.

It’s time to save your flow and move to the top of your flow. In the HTTP trigger, click the Copy icon to copy the HTTP GET URL value. You will need it in your Power BI report in the next section.

The Power BI Implementation

It’s time to open the pbix file of the report you wish to track. Make sure that you only track reports and data according to your company data protection policies. Open the Power BI file using Power BI Desktop.

In Home tab, click Enter Data. Replace Column1 with Pixel URL Template, and paste the HTTP GET URL value from the Power Automate trigger of the previous section into the table cell. Then, name the table Pixel and click Load.

In my PoC, I wanted to track users by the selections they make from a simple table of cities and their amount.

Under the Pixel table, you can create a new measure for the selected data you wish to track. For example, to load the values of the user’s selected cities and their amounts from the table Report Data above, you can use this measure:

Selected Cities = CONCATENATEX (
    'Report Data',
    'Report Data'[City] & ": " & 'Report Data'[Amount],
    ", "
) 

Next, create another measure in the Pixel table for the URL:

URL = SUBSTITUTE (
    SELECTEDVALUE ( Pixel[Pixel URL Template] ),
    "{data}",
    [Selected Cities] & ";" & USERNAME()
)

The URL measure substitutes the placeholder {data} of the Power Automate trigger URL with the tracked data which includes the following components:

• The selected cities in the format City 1: Amount 1, City 2: Amount 2, …, City N, Amount N (Using CONCATENATEX of the previous measure).
• A semicolon character
• The logged-in user’s email (using the USERNAME DAX function).

Change the Data Category of the URL measure to Image URL.

Open the page you wish to track in your report and create a Table visual. Move the URL measure into the Values section of the visual.

To hide Power BI Pixel from users set the Grid’s Outline Color and the Column Headers’ Font color of the table to be the same color as your page’s background. You can also minimize the size of the table visual to its minimal size.

Important note: The recommendation to hide the Power BI Pixel here is given for user-experience purposes. Don’t do it to spy on your users, or leak data 🙂 Make sure you don’t violate any corporate policies or privacy agreements by doing so. Your users should be notified of the use of this tracking technique.

Now you can publish the report in the service and start collecting the data via the Power Automate flow.

Security Considerations

The Power BI Pixel technique can be exploited by Power BI report developers to leak confidential data outside of your organization. If you use this technique to legitimately track usage, make sure you follow your company’s data protection and privacy policies before you implement this technique.

Don’t share your PBIX files with untrusted sources who can add Power BI Pixels to your reports. While this technique has a size limit for the data you can send in the URL, it can still be used to retrieve aggregated information.

Security auditors should take a closer look at this technique and start planning for a careful analysis of the measures in reports. Measures that are formatted as Image URLs should be inspected by both static and dynamic means (A static and quick review of the DAX code may not be sufficient since measures can be intentionally implemented in a very complex and obscure manner).

• A static analysis can include manual or automated analysis of the measures that are defined in the PBIX file to search for suspicious logic.
• A dynamic analysis relies on XMLA endpoint tools such as DAX Studio to read the measure output and find whether the URL includes confidential data in the path or parameters.

Conclusions and Thoughts

In this article, you have learned how to implement your own Power BI Pixel to track users and the selections they make in report pages. You can test the proof-of-concept here (if you have the permissions to install private Template Apps), or follow the step-by-step walkthrough to build your own Power BI Pixel with a low-code approach using simple measures and a Power Automate flow.

While there are serious data protection and privacy considerations to examine before you implement such a solution, you may find it as the only way right now to track your mission-critical reports and measure their effectiveness based on users’ selections on slicers and visuals.

Stay tuned for updates in this blog post. I wouldn’t be surprised if Microsoft introduces new capabilities to monitor or limit the use of this technique to simplify its governance.

If you wish to implement the Power BI pixel in your enterprise and get the source files plus a one-hour paid support session from a Microsoft MVP, contact me here. This service can be provided for in-house Power BI developers only.