Have you ever wanted to search through your Power BI measures, calculated columns or calculated tables for a specific function or table/column reference? Or perhaps being able to export Power BI DAX measures for documentation purposes? If this is the case then this post is for you.
As a Power BI dataset admin or author, keeping track of and documenting the data is an important task. However, keeping track of all the measures and DAX logic used in a dataset can be a burdensome task. Especially as the dataset grows over time. Plowing through hundreds or thousands of measures with complex logic, reused logic or multiple layers of references to other calculations can quickly get out of hand.
In this post I will show you how to use Dynamic Management Views (DMVs) in DAX Studio to query and export Power BI DAX measures and metadata about your DAX measures. You can then use this data to further analyze your logic and metadata in the tool of your choice, e.g. Excel.
What is DAX Studio
DAX Studio is a third party tool that allows for development and troubleshooting (and more) of analysis services tabular model, including Power BI datasets.
You can download it at daxstudio.org
Dax Studio is a free, third party software developed and maintained by Darren Gosbell https://darren.gosbell.com/
DAX Studio is commonly described as the ultimate tool for writing, authoring and analyzing DAX queries.
Check out my article about how to level up your Power BI model with DAX to find out how to to enhance your productivity with Power BI.
Connect DAX Studio to a Power BI dataset
There are two ways to connect to your Power BI dataset from DAX Studio. You can either choose to open DAX Studio directly and connect to an existing dataset. The model can be a Power BI desktop file or a model hosted on a Tabular server, e.g. a dataset hosted in a Power BI dataset (Premium or Premium per user is needed)
You can also choose to open DAX Studio from Power BI External Tools ribbon.
Opening DAX Studio directly from Power BI Desktop will automatically create a connection between DAX Studio and your Power BI dataset.
Get that (meta)data!
In order to export the DAX objects information from the dataset, you need to query some DMV’s(Dynamic Management Views), which you can access and query from DAX Studio.
The query language is called Data Mining Extensions (DMX) and is based on SQL syntax. However, it doesn’t support the full SELECT statement syntax. You can learn more about the query syntax on MS learn here.
Query DAX Measures
A list of all DAX measures is available in DMV TMSCHEMA_MEASURES. Except from the actual name and DAX expression it also contains some metadata information. For example data type, format string, modified time and display folder
You can run the following query to get a full list of all measures and their attributes
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES
Query DAX Calculated Columns
A list of all DAX calculated columns is available in DMV TMSCHEMA_COLUMNS. However, this DMV also contains a complete list of all columns available in the model. You can use a filter on attribute [Type] in order to filter out your DAX calculated columns.
You can run the following query to get a full list of all calculated columns and their attributes
select * from $SYSTEM.TMSCHEMA_COLUMNS WHERE [Type] = '2'
Except from the actual name and DAX expression it also contains some metadata information. For example data type, format string, modified time, sort by column and refresh time
Exporting data from DAX Studio
The default output setting of a DAX Studio query is a Results Table. However, the output can be changed to a file (csv, txt) or an excel file. For example, in order to change the output to a static Excel file, choose Home -> Results -> Excel (Static). This will open a save dialogue after executing the query.
You can also save the query results to the clipboard or a linked Excel file.
One of the limitations of the DMX query language is the lack of certain WHERE operators. Therefore, the best way to analyze and free-text search the data is to export the data to an Excel file and do those types of tasks from there.
Thank you!
Do you know any other way of exporting this type of model metadata? Or are you already using DMV’s in any other scenario? Please let me know in the comments below.
Thank you for reading!
You can also save the VPAX file with DAX Studio – it includes all the DAX expressions (not only the measures) and loading the VPAX file with VertiPaq Analyzer, you can also format all the DAX expressions displayed in a single Excel table.https://www.sqlbi.com/tools/vertipaq-analyzer/
It requires two more clicks, but it’s easier to archive data model and measures in a single file.
Thank you Marco for the additional tip! I might add it to the post later on
Is there a way to schedule one of these DMX queries?
Hi Chris, Here’s a couple of articles describing how to query metadata using Azure data factory and Power BI. However, in order to query a Power BI dataset hosted in the Power BI service, you need to use XMLA endpoint and therefore PPU or Premium is a must.
https://www.tackytech.blog/how-to-query-and-copy-data-from-power-bi-datasets-via-azure-data-factory/
https://www.tackytech.blog/how-to-build-meta-reports-on-top-of-power-bi-datasets-with-dynamic-management-views-dmvs/
https://www.fourmoo.com/2021/11/24/how-did-i-keep-my-power-bi-dataset-measures-documented-and-up-to-date/?utm_source=rss&utm_medium=rss&utm_campaign=how-did-i-keep-my-power-bi-dataset-measures-documented-and-up-to-date