To get insights on who clicked what in Power BI, you can use a combination of built-in features, custom logging, and Azure services. Here’s a step-by-step guide:

FOR ADMINS

1. Enable Auditing and Usage Metrics in Power BI:

Power BI has built-in auditing and usage metrics that can help track user activities. To enable and use these features, follow these steps:

a. Enable Audit Logs:

  • Go to the Power BI Admin portal.
  • Navigate to “Audit logs” under the “Admin portal” section.
  • Turn on auditing to start collecting log data.

b. Usage Metrics Reports:

  • Open your Power BI workspace.
  • Navigate to the dataset or report for which you want to see the usage metrics.
  • Click on the “More options” (three dots) next to the report name.
  • Select “Usage metrics report.”

This report will give you insights into user interactions with the report, including who viewed the report and when.

2. Custom Logging using Power BI Embedded and Application Insights:

For more detailed tracking, such as specific clicks or interactions within a report, you can use Power BI Embedded with Azure Application Insights.

a. Set up Power BI Embedded:

b. Integrate Application Insights:

  • Set up Azure Application Insights to collect telemetry data from your application.
  • Instrument your application to log custom events when users interact with the Power BI report.

For example, you can log custom events whenever a user clicks a specific element in the report:

Javascript Copy code

// Sample JavaScript code to log custom events in Application Insights
var appInsights = window.appInsights || function(config) {
    function s(config) { t[config] = function() { var i = arguments; t.queue.push(function() { t[config].apply(t, i) }) } }
var t = { config: config }, r = document, f = window, e = “script”, o = r.createElement(e), i, u;
    for (o.src = config.url || “//az416426.vo.msecnd.net/scripts/a/ai.0.js”, r.getElementsByTagName(e)[0].parentNode.appendChild(o), t.queue = [], t.version = “2.0”, i = [“Event”, “Exception”, “Metric”, “PageView”, “Trace”, “Dependency”]; i.length;) s(“track” + i.pop());
    return s(“setAuthenticatedUserContext”), s(“clearAuthenticatedUserContext”), t
}({    instrumentationKey: “YOUR_INSTRUMENTATION_KEY”}); window.appInsights = appInsights;
appInsights.trackPageView();

Replace “YOUR_INSTRUMENTATION_KEY” with your actual Application Insights instrumentation key.

3. Analyze the Data:

Once you have the data in Application Insights, you can use its powerful querying capabilities to analyze user interactions:

Kql Copy code

// Sample Kusto Query Language (KQL) to query custom events in Application Insights
customEvents
| where name == “ClickEvent”
| summarize count() by tostring(customDimensions.userId), bin(timestamp, 1d)
| order by timestamp desc

This query will show you the count of click events by user ID, grouped by day.

Summary

  • Enable Audit Logs and Usage Metrics in Power BI to get basic usage information.
  • Use Power BI Embedded and Azure Application Insights for detailed tracking of user interactions.
  • Analyze the collected data using Application Insights to get insights into who clicked what in your Power BI reports.

By combining these methods, you can achieve comprehensive tracking of user interactions in Power BI.

Note:

If you don’t see the “Usage metrics report” option and only see “Get quick insights,” it might be because of the following reasons.

  • You may not be on a premium workplace
  • Permissions: You might not have the necessary permissions to access usage metrics for the report or dataset.
  • Report Type: Usage metrics are available for reports and dashboards, not datasets.
  • Service Version: Ensure you are using the latest version of Power BI Service.

Here’s what you can do to resolve this:

  • Check Permissions
    • Ensure you have the necessary permissions to view usage metrics:
    • You need to be an Admin, Member, or Contributor of the workspace where the report resides.
  • Verify the Report Type
    • Ensure you are trying to access usage metrics for a report or dashboard, not a dataset.
  • Use the Admin Portal
    • If you are an admin, you can access tenant-wide usage metrics via the Power BI Admin Portal.
    • Navigate to Admin Portal:
      • Open Power BI Service.
      • Click on the settings gear icon in the upper right corner.
      • Select “Admin portal.”
    • Usage Metrics:
      • In the Admin portal, go to “Usage metrics.”
      • Here, you can view and analyze usage data for reports, dashboards, and datasets across the tenant.
  • Enable Usage Metrics for the Workspace
    • If the usage metrics feature is not available, you might need to enable it for the workspace:
    • Open Workspace Settings:
      • Go to the workspace where your report is located.
      • Click on the settings gear icon.
      • Select “Settings.”
    • Enable Usage Metrics:
      • In the workspace settings, ensure that the “Usage metrics” option is enabled.
  • Custom Solution with Power BI REST API
    • If the built-in options are not sufficient, you can use the Power BI REST API to extract detailed usage metrics.
    • Register an Application:
      • Register a new application under Azure Active Directory.
    • Set API Permissions:
      • Grant the registered application the necessary API permissions for Power BI service.
    • Use Power BI REST API:

Example: Using Power BI REST API with Python

Here’s a basic example of how you might use Python to access the Power BI REST API:

Python code sample
import requests
# Set your parameters
TENANT_ID = 'your_tenant_id'
CLIENT_ID = 'your_client_id'
CLIENT_SECRET = 'your_client_secret'
RESOURCE = 'https://analysis.windows.net/powerbi/api'
AUTHORITY_URL = f'https://login.microsoftonline.com/{TENANT_ID}'
TOKEN_URL = f'{AUTHORITY_URL}/oauth2/token'
API_URL = 'https://api.powerbi.com/v1.0/myorg/admin/activityevents'
# Get the access token
data = {
    'grant_type': 'client_credentials',    'client_id': CLIENT_ID,    'client_secret': CLIENT_SECRET,    'resource': RESOURCE}
response = requests.post(TOKEN_URL, data=data)

access_token = response.json().get('access_token')

# Define headers
headers = {
'Content-Type': 'application/json','Authorization': f'Bearer {access_token}'
}
# Make API request
response = requests.get(API_URL, headers=headers)
usage_data = response.json()
 
# Print the usage data

print(usage_data)
  • Custom Logging using Power BI Embedded and Azure Application Insights
    • As mentioned earlier, if you need even more detailed tracking of user interactions within reports, consider using Power BI Embedded and logging custom events to Azure Application Insights.
    • By following these steps, you should be able to access and analyze usage metrics for your Power BI reports, even if the “Usage metrics report” option is not directly available.

Export and review the usage metrics

To export and review the usage metrics for your Power BI reports and dashboards in a list format, you can follow these steps:

Steps to Export Usage Metrics in Power BI

  1. Navigate to Your Workspace:
  1. Open Power BI Service.
  2. Go to the workspace where your reports and dashboards are located.
  3. Open the Usage Metrics Report:
  1. Click on the “More options” (three dots) next to the report or dashboard name.
  2. Select “View usage metrics report.” If this option is not available, make sure you have the necessary permissions or try accessing the Admin portal for usage metrics.
  3. Customize and Export the Report:
  1. Once the usage metrics report is open, you can customize it by adding/removing visualizations, changing filters, etc.
  2. Click on the “File” menu.
  3. Select “Export” and then choose “PowerPoint” or “PDF” to export the report in a visual format. However, for a list format, you need to use the data export option.
  4. Export Data to CSV or Excel:
  1. Click on any visual within the usage metrics report.
  2. Click on the “More options” (three dots) for that visual.
  3. Select “Export data.”
  4. Choose “Summarized data” or “Underlying data” based on your needs.
  5. Select the file format (CSV or Excel).
  6. Click “Export.”

Reviewing Exported Data in Power BI Desktop

  1. Open Power BI Desktop:
  2. Launch Power BI Desktop on your computer.
  3. Import the Exported Data:
  1. Click on “Home” and then “Get Data.”
  2. Select the file format you exported (CSV or Excel).
  3. Locate the exported file and load it into Power BI.
  4. Create Custom Reports:
  1. Use Power BI Desktop to create custom reports and visualizations based on the exported usage metrics data.
  2. You can use tables, charts, and other visuals to analyze the data in detail.

Using Power BI REST API for Automated Export

For automated data extraction and more comprehensive usage metrics, you can use the Power BI REST API. Here’s a basic example using Python to extract and export usage metrics data:

Example: Using Power BI REST API with Python

  1. Register an Application in Azure:
  1. Go to the Azure Portal.
  2. Register a new application under Azure Active Directory.
  3. Set API Permissions:
  4. Grant the registered application the necessary API permissions for Power BI service.
  5. Use Power BI REST API:
Python Copy sample

import requests

import pandas as pd

# Set your parameters

TENANT_ID = 'your_tenant_id'

CLIENT_ID = 'your_client_id'

CLIENT_SECRET = 'your_client_secret'

RESOURCE = 'https://analysis.windows.net/powerbi/api'

AUTHORITY_URL = f'https://login.microsoftonline.com/{TENANT_ID}'

TOKEN_URL = f'{AUTHORITY_URL}/oauth2/token'

API_URL = 'https://api.powerbi.com/v1.0/myorg/admin/activityevents'

# Get the access token

data = {

'grant_type': 'client_credentials',

    'client_id': CLIENT_ID,

    'client_secret': CLIENT_SECRET,

    'resource': RESOURCE

}

response = requests.post(TOKEN_URL, data=data)

access_token = response.json().get('access_token')

# Define headers

headers = {

    'Content-Type': 'application/json',

    'Authorization': f'Bearer {access_token}'

}

# Make API request to get activity events

params = {

    'startDateTime': '2023-01-01T00:00:00Z',

   'endDateTime': '2024-01-01T00:00:00Z'

}

response = requests.get(API_URL, headers=headers, params=params)

activity_data = response.json()

# Convert to DataFrame

activity_df = pd.DataFrame(activity_data['activityEventEntities'])

# Export to CSV

activity_df.to_csv('powerbi_usage_metrics.csv', index=False)
print("Usage metrics data exported successfully.")

Summary

  • Export usage metrics directly from Power BI Service using the “Export data” option for visuals.
  • Import and review the data in Power BI Desktop for detailed analysis.
  • Use Power BI REST API for automated data extraction and export.

By following these steps, you can easily export and review usage metrics for your Power BI reports and dashboards.

Usage Metrics in Power BI How to Guide
Tagged on: