In 2022, I wrote a popular post about how you can export your DAX measures using DAX Studio, along with other valuable Metadata from Power BI Semantic Models. This is great way to get metadata about your model and allows you to export and store it in e.g. excel format.
In December 2023, Power BI introduced a set of new DAX information functions, functioning similarly to the Dynamic Management Views (DMV) discussed in my previous post. These new DAX functions allow you to retrieve metadata from your semantic model using DAX within Power BI.
DAX (Data Analysis Expressions) is a functional language used in Analysis Services Tabular and Power BI. It is used to create definitions of calculations on top of a dataset. It has a similar syntax as expressions used in Excel. However, DAX operates over tables and columns, where Excel operates over cells. Even though the language has it similarities they do not function the same way.
In combination with this, Microsoft recently released a new function in Power BI desktop called DAX Query View. This new view in Power BI desktop allows you to run DAX queries against your semantic model. Since the DAX language now also includes informational metadata functions, you can use these to extract metadata information about your model.
❕ Note – The new DAX Query view is currently in preview as part of Power BI Desktop November 2023 release. To enable the preview feature, go to File -> Options and settings -> Options -> Preview features and enable “DAX Query View”. The DAX Query View feature is showcased as part of Power BI Desktop’s November 2023 release.
In this post, we will go through how to use the new DAX Query View as well as how to extract metadata about your Power BI semantic model using the new DAX information functions.
DAX Query View
The DAX Query View can be found as a separate tab in Power BI desktop, below Report, Table and Model view.
The DAX Query View allows you to execute DAX queries against your semantic model. There are some code editor functionalities such as commenting, search & replace and query formatting options available. There’s also a command palette that let’s you see all commands available, such as…
If you are somewhat familiar with Power BI and DAX, you might know how to write DAX measures and use them in Power BI reports. There is a key difference between writing a DAX measure and writing a query in DAX Query View. A measure always needs to return a scalar value (single value) where a DAX query in the Query View returns a table.
This is similar to how you use DAX to query data in e.g. DAX Studio and similar to how DAX queries your semantic model before data is presented in your reports.
Query Metadata from Power BI Semantic Models using DAX
The DAX Query View enables you to query metadata information using DAX Information functions.
In this example, we are using the INFO.MEASURES function. At the time of writing this article, there are 52 different information functions available. You can find more information about them on dax.guide
Viewing the results
Lets take a look at the function INFO.MEASURES. In this example, we are executing the following DAX query:
After executing the DAX code we are presented with the results in the results section. As you can see from the results, we have 8 rows in the result, each containing information about one measure in our semantic model.
By using INFO.MEASURES() you can retrieve information such as
- Name of the measure
- Which table the measure belongs to
- Description of the measure
- Data type of the measure
- The DAX expression
- Format string
- If the measure is hidden or not
- Display folder
- Last modification datetime
Exporting the results
You are able to export the results by using the copy button above the results section. This allows you to copy the table to the destination of your choice. For example, an Excel sheet or a static table in Power BI. Currently there are no automated ways of exporting the results to e.g. an Excel file, as you can do in DAX Studio. However, these DAX functions opens up the possibility to not only query your semantic model using Dynamic Management Views (DMVs), but also by using DAX!
By using the new Information functions in DAX, combined with DAX Query View in Power BI Desktop, you are now able to extract Metadata from Power BI Semantic Models in Power BI Desktop. This allows you to view the data in a user friendly environment.
These DAX functions offers a great way of exporting metadata information about your model that you might want to use for documentation purposes. Documenting your semantic model manually can prove to be quite tedious work. Therefore, being able to query metadata information about your model, including tables, columns, and measures is very valuable.
Thank you for reading
Thank you for reading! If you liked this article or learned something from it, please comment below.
Make sure to subscribe below to get notified when new content is released on this site.