Getting all DAX expressions across all models and reports in your Power BI tenant, can be very useful. This would include measures, report-level measures as well as calculated tables and calculated columns (even though we will focus on the measures in this blog post).
Use cases:
-Maybe you want to make sure that everyone is sticking with the correct definition / calculation of a KPI or measure.
-The other way round, you might want to see which expressions are duplicates so you can guide people to using just one measure instead of building their own.
We have 2 options for this:
1) Using the WorkspaceInfo API calls (Scanner API)
Pros
Free
Cons
Requires programming skills and parsing JSON files
Time intensive
Does not include report-level measures
Requires Fabric Administrator or Power Platform Administrator role
The PostWorkspaceInfo call is limited to 100 workspaces per call
2) Measure Killer's Tenant Analysis
Pros
Easy to use
Includes report-level measures
Finds duplicates automatically (parses out whitespace and comments)
No limitations regarding the number of workspaces or models
Easy export of DAX expressions as .json
Admin rights optional
Cons
Part of the paid version of Measure Killer
External software (client application)
Option 1: WorkspaceInfo API Calls
This method involves using two API calls (WorkspaceInfo - PostWorkspaceInfo and GetScanResult) to get all expressions in semantic models. You’ll need some programming skills and knowledge of how to handle API requests.
Requirements:
Fabric Administrator or Power Platform Administrator role.
Two tenant settings must be enabled: Enhance admin APIs responses with detailed metadata and Enhance admin APIs responses with DAX and mashup expressions
Using Python (version 3.12.4), the example below shows how to get all expressions in semantic models of 2 workspaces.
Replace the workspace_list (list of workspaces ids) accordingly.
from azure.identity import InteractiveBrowserCredential import requests import time # Replace the following list with the workspaces you want to scan. This is limited to 100 workspaces workspace_list = [ "workspace_id_1", "workspace_id_2" ] # Do not change the following code: api = "https://analysis.windows.net/powerbi/api/.default" authority = "https://login.microsoftonline.com/" auth = InteractiveBrowserCredential(authority=authority) access_token = auth.get_token(api).token url="https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?datasetExpressions=true&datasetSchema=true" payload = {"workspaces": workspace_list} header = {"Authorization": f"Bearer {access_token}"} response = requests.post(url, headers=header, json=payload) wk_post_data=response.json() scan_id = wk_post_data.get('id') #The scan id, will be passed to the get scan results api call #Sleeps for 2 seconds to allow the scan to complete #! This will probably require a longer sleep time for larger workspaces or checking for the status of the scan using the GetScanStatus API time.sleep(2) #Get the actual results results_url=f"https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanResult/{scan_id}" results=requests.get(results_url, headers=header) print(results.json()) |
Option 2: Measure Killer
Using Measure Killer's tenant analysis mode, users can easily scan their entire tenant, including all DAX expressions in all models and reports. There is a function to get all duplicate DAX expressions in your tenant. If you want to do the search yourself you can also export all expressions as a .json file.
Let's now find the duplicates, there is a button on the top right for "Duplicate DAX Expressions".
Initially duplicates per model are selected but I already changed this to "Duplicates on a tenant level". If you have thousands of models this search can take 1-2 minutes or even longer.
As you can see this will also list calculated columns, I am not that much interested in them since they actually come from local date tables which should not be in the model anyway.
For the "measure" I chose, there are 3 other measures with the same DAX expression. Measure Killer currently takes out any comment or whitespace when finding duplicates. The expression is SUMX(factData, 1) and we can also see the names of the other measures which are just "measure". More interesting is which semantic models they belong to however.
This is an extremely powerful feature when having a larger tenant or thinking about converging KPIs inside an organization. It also gives us a nice insight into what people are buildling on top of models in report-level measures.
Conclusion
Many options are available for accessing all DAX expressions in a single Power BI model. However, if you want to expand this and access all DAX expressions in your Power BI tenant, your options are more limited. You can either use your programming skills with Power BI REST API calls or use Measure Killer's Tenant Analysis mode for an easy, out-of-the-box experience. Changing the queries slightly or using Measure Killer will also allow you to extract all M expressions in your tenant.
Komentáre