Having easy access to all DAX expressions in a Power BI model, or even across all models in your Power BI tenant, can be very useful. This is especially true if you need to search through expressions of measures, calculated tables, and columns. Additionally, you may want to search for duplicated expressions to avoid redundancy.
This blog post will demonstrate several options to achieve this, for both a single report and an entire tenant or a group of workspaces. A similar approach can also be used to get all M expressions in your tenant if needed.
Scenario 1: A single Power BI model
For a single model, this can be easily achieved using two methods: the new DAX Query view feature or Measure Killer.
Option 1: Using the new DAX query view in Power BI Desktop
You can use the new DAX Query view and run the following query to get all the DAX expressions in your model. Note that this query will not include report-level measures (measures built in live/thin reports) since they are part of the report's definition.
Advantages:
Built into Power BI Desktop.
Disadvantages:
Requires some DAX coding.
No automatic search for duplicated expressions.
Not possible to include expressions from report level measures
EVALUATE VAR _partitions = // DAX expressions of calculated tables SELECTCOLUMNS( INFO.PARTITIONS("Type",2), //Filtering for type = 2: Calculated tables "ID",[TableID], "TableID",BLANK(), "Expression",[QueryDefinition])
VAR _tables= SELECTCOLUMNS( INFO.TABLES(), "ID",[ID], "TableID",BLANK(), "Name",[Name] ) VAR CalculatedTables = //Merge with INFO.TABLES() to get the name of each table ADDCOLUMNS( NATURALINNERJOIN(_tables,_partitions), "Type", "Calcualted table")
VAR _columns = // DAX expressions of calculated columns SELECTCOLUMNS( INFO.COLUMNS("Type",2), //Filtering for type = 2: Calculated columns "ID",[ID], "TableID",[TableID], "Name", IF(NOT ISBLANK([ExplicitName]), [ExplicitName], [InferredName]), "Expression",[Expression], "Type","Calculated column" ) VAR _measures_exp = // DAX expressions of measures SELECTCOLUMNS( INFO.MEASURES(), "ID",[ID], "TableID",[TableID], "Name", [Name], "Expression",[Expression], "Type","Measure" ) RETURN UNION(_columns,CalculatedTables,_measures_exp)
|
Option 2: Measure Killer (Free)
Download Measure Killer (link) and run it for your model. Navigate to the DAX Expressions tab to filter, search, and identify duplicated expressions easily. You can also export all expressions as Excel or JSON files.
Advantages:
Includes report-level measures expressions (using mode 2 - shared model on local machine).
Easy to use, no coding required.
Disadvantages:
Requires a client installation.
Scenario 2: Tenant-Wide Search
For tenant admins, there may be situations requiring a search through DAX expressions for several models or even all models in the Power BI service. This post presents two approaches: using the WorkspaceInfo API calls (Scanner API) and Measure Killer's Tenant Analysis.
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()) |
Advantages:
Free option.
Disadvantages:
Requires programming skills and parsing JSON files.
Does not include report-level measures.
Requires Fabric Administrator or Power Platform Administrator role.
The PostWorkspaceInfo call is limited to 100 workspaces per call.
Option 2: Measure Killer (Premium - paid)
Using Measure Killer's tenant analysis mode, users can easily scan their entire tenant, including all DAX expressions in all models. You can also use the custom export feature to extract all expressions into an easy-to-read JSON file. To run Tenant Analysis and get all the DAX expression, first select the models and reports you want to analyze and click on run. After the process is finished, simply go to the DAX expressions tab.
Requirements:
Installation of Measure Killer
Fabric Administrator or Power Platform Administrator role.
Any Measure Killer Enterprise license
Advantages:
Easy to use, no programming skills required.
Includes all report-level measures in your tenant.
Allows searching for duplicates.
No limitations regarding the number of workspaces or models.
Dedicated export for DAX expressions.
Disadvantages:
Part of the paid version of Measure Killer.
Requires a client installation.
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.
コメント