In the realm of Power BI development, creating reports without a solid understanding of measure DAX code can be a daunting task. Measures in Power BI can have complex codes, logic, calculations across multiple tables, and the utilization of various DAX functions. When developers lack a solid grasp of the measure DAX code, it becomes challenging especially when aiming to create thin reports. Thin reports refer to reports that connect to an existing dataset on Power BI Service, utilizing the Connect Live connectivity mode, thereby enabling multiple reports to be connected to a single dataset.
Video
One solution for this is to run the below C# script on Tabular editor to add a measure expression to the measure description, then you will be able to hover the mouse over the measure to read the expression:
foreach(var m in Model.AllMeasures)
{
m.Description = m.Expression;
}
This method as Bernat pointed has some big issues:
1- Nobody guarantees that the dax code of the measure has not changed after the script was run 2- It's not easy to read a long expressions
3- it's hard to look at the code in such small font
The method is good, but it must be done manually by developers for each report, and if someone forget to run the script after updating the report, the description will be incorrect.
The solution presented in this blog post addresses these challenges by automating the extraction and documentation of measure details. By leveraging the capabilities of Power Automate and Power BI REST APIs, developers can streamline the process of retrieving measure expressions, ensuring accurate representation in their reports. In this method, we will create a Power Automate flow that scans your Power BI tenant, creates a CSV file and record all measures details on it, and sends it to you so you can use it in Excel or integrate it with other software.
I used Power BI Rest APIs with Power Automate for this solution, if you're interested in learning all about setting up a HTTP action on Power Automate to run an API, please read this blog first:
The overall view of this solution is as follows:
1- Comprehensive Workspace scan: Admin-Level Power BI Service Scanning
The initial step in the process involves scanning your Power BI service as an administrator to identify and retrieve information about all workspaces within your tenant. This is achieved by utilizing the "WorkspaceInfo GetModifiedWorkspaces" API, which requires the inclusion of specific parameters.
This API has 3 Parameters: 1- excludeInActiveWorkspaces: to exclude inactive workspaces
2-excludePersonalWorkspaces: to exclude personal workspaces
3-modifiedSince: Last modified date (must be in ISO 8601 compliant UTC format)
In this blog, I want to exclude the personal and inactive workspaces, so the flow starts with an HTTP action that calls an API with the following URL:
GET https://api.powerbi.com/v1.0/myorg/admin/workspaces/modified?excludeInActiveWorkspaces=true&excludePersonalWorkspaces=trueResult: All active Workspaces' IDs within your organization excluding Personal Workspaces
* How to fill the advanced options of the HTTP action in Power Automate has been fully explained in this post: https://www.vahiddm.com/post/call-power-bi-rest-api-with-power-automate As a result of this action, all workspace IDs are available in JSON format as follows:
[
{
"Id": "3740504d-1f93-42f9-8e9d-c8ba9b787a3b"
},
{
"Id": "19cb346c-9839-4e19-81e6-76364d0b836f"
}
]
To transform the output format from multiple objects to a unified array of IDs, which can be utilized as the body of another API in the subsequent step, add the SELECT action as depicted in the image to the flow:
2- Scan all workspaces within your organization and extract required metadata
In the preceding section, we used the "WorkspaceInfo GetModifiedWorkspaces" API to obtain a list of workspace IDs in the organization. To proceed, we need to scan all these workspaces and extract DAX codes for all measures using the "WorkspaceInfo PostWorkspaceInfo" Power BI Admin API. This API scans the specified workspaces, retrieves metadata about them, and offers five parameters, which are comprehensively explained on the documnet page. In this flow, we need to use the datasetExpressions and datasetSchema parameters in the API's URL, setting them to true, and utilize the POST method. To utilize the "WorkspaceInfo PostWorkspaceInfo" API, a request body must be prepared, containing a list of workspace IDs, like this:
{
"workspaces": [
"97d03602-4873-4760-b37e-1563ef5358e3",
"67b7e93a-3fb3-493c-9e41-2c5051008f24"
]
}These IDs can be obtained from the previous action; however, they need to be appropriately modified to serve as the API body. To accomplish this, include a Compose action in the flow and configure it as follows:
* the Output in the inputs box is from the previous action, Select.
With all the necessary components in order, it is time to incorporate the "WorkspaceInfo PostWorkspaceInfo" API into the flow. Add a new HTTP action using the POST method, and set the output of the previous action, Compose, as the body. The URL for this action needs to be as follows:
https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?datasetExpressions=true&datasetSchema=trueRequired Permission:
Required Scope
Limitations
Upon executing the aforementioned API, you will receive a simple JSON output consisting of three keys: id, createdDateTime, and status. The id represents a scan id that is essential for retrieving the scan result using another admin API called "WorkspaceInfo GetScanResult." Using this API is a straightforward process, requiring only the scan id obtained from the previous action. To employ this API, simply add a new HTTP action to the flow, as illustrated in the following image, with the specified URL:
https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanResult/@{body('WorkspaceInfo_PostWorkspaceInfo').id}@{body('WorkspaceInfo_PostWorkspaceInfo').id} is a Power Automate formula that can be added in the Expression tab. It returns id from the previous action (named WorkspaceInfo_PostWorkspaceInfo).
The resulting output of this API is a complex JSON structure, comprising numerous identical arrays and objects. In order to facilitate the extraction and utilization of the desired items from this JSON, it is necessary to parse it accordingly. While using the "Generate from sample schema" approach for parsing, you may encounter multiple objects with the same names, such as ID, NAME, DATE, and Expression.
To address this issue, I recommend referring to the custom schema provided in the accompanying blog post: https://www.vahiddm.com/post/custom-json-schema-to-simplify-parsing-of-power-bi-scanner-api-output-for-power-automate. This schema includes titled properties for each item, enhancing the clarity and ease of parsing the Power BI Scanner API output within Power Automate.
3- Extraction of Measures details from Workspaces, Datasets, and Tables
To extract all measures' data from the previous complex JSON output, a series of loops are implemented to scan workspaces, datasets, tables, and measures.
Scan workspaces -> Datasets -> Tables -> Measures
Before incorporating the loops, it is crucial to establish a variable in the flow to store the measure details. To achieve this, utilize the Initialize variable action, assigning it the name "Result," selecting the type as Array, and leaving the value blank. It is important to note that this variable can't be created inside a loop.
The initial two loops can be seamlessly integrated into the flow without any specific conditions. However, it is necessary to introduce a condition prior to the third and fourth loop, known as the Measure Loop. This condition ensures the exclusion of datasets without any tables (such as streaming/push datasets), tables without any measures, and the omission of all Usage Metric Datasets (each workspace typically has one usage metric dataset). Add these loopes and conditions to the flow as below:
Note: These loops will be automatically added to your flow when you select the corresponding items from the output of the Parse JSON - Scan Output action.
In the condition, leave the if no section blank, and in the if yes section, add a Compose action to create an object for each measure with keys and values representing the measure details. Then, include an Append to array variable action to add each measure object to the variable that was created before the loops. The actions inside the if yes section should be configured as follows:
JSON object literals are surrounded by curly braces {} and contains Key/value pairs:
As a result, the details of all the measures in your Organization Power BI service, has been saven in the variable and you can save them as a CSV file and save it on you computer, SharePoint, or send it to youe email.
In this blog I used the email method, so first a Create CSV table action need to be added to the flow (outside of those loops) and then send that CSV to your email using the Send an email (V2 or V3) action like this image:
Note:
1- You can save the measures details directly on a Power BI hybrid dataset by using the Add rows to a dataset in the if yes section of the loop (it will be a little bit slow). You can also add an action to restart that hybrid dataset, read this blog: https://www.vahiddm.com/post/restart-power-bi-hybrid-dataset-with-power-automate-and-apis
2- Measures won't change every hour, so you don't need to schedule this flow every hour
3- Number of Measures Matters: I tested this flow with different scenarios. It takes 8~12 minutes to extract 1000 measures from 10 workspaces and it takes 1~1.5 to extract 20000 measures. (Thanks to Štěpán Rešl for his helpful Number of Measures Matters blog post)
Conclusion:
With the automation capabilities offered by Power Automate and the seamless integration with Power BI REST APIs, the process of measure discovery and documentation becomes significantly streamlined. By leveraging this powerful technique, Power BI developers can unlock their full potential, improve productivity, and create impressive reports with ease.
Great post. Thanks for sharing