Export Measures and Calculated columns

Documenting DAX formulas is always a good practice as it saves a lot of time in various scenario such as when your PBI report having a good amount of DAX formulas and you want to review them at once, or wish to share with colleagues or save DAX work for future reference.

Definitely you would rather like to refer documentation than re-open a Power BI file then click on each measure to see DAX formula.

Here you will see how to export all available measures and calculated columns that you have created in your Power BI model into excel file.

Prerequisites : You must have an installed DAX Studio, and Power BI desktop in your machine, and Power BI file ( having measures or calculated columns) to connect with DAX studio.

Now follow the below steps:

Step 1: Open your Power BI file first, then open Dax studio and click on Connect tab.

Step 2: Connect your Power BI file with DAX studio, if you have opened multiple Power BI files then you can select desired file from drop down list.

Step 3: Under Metadata panel you will see a data model which contains tables, columns and measures.

Step 4: Now we will see how to list out all the measures which are available Data Model.

First click on DMV tab, then in Search box type “Measure” to sort out the DMV lists.

After that double click on DMV: MDSCHEMA_MEASURES, and you will see a query is generated automatically in Editor.

Step 5: Lets run the query, and you can see it returns details of all the available measures in data model in Results window and when you scroll to the right side you can see more details.

select *
from $SYSTEM.MDSCHEMA_MEASURES

You can select only relevant fields also can refine query result by adding condition as shown below.

select MEASUREGROUP_NAME, MEASURE_NAME, EXPRESSION
from $SYSTEM.MDSCHEMA_MEASURES
where MEASURE_AGGREGATOR = 0

Step: 6: Lets Export Measures into Excel file. For this you need to change output as Excel.

Click on Output table, then select Excel as Static.

Step 7: Now Run the query again and you can see it asks to save query result into Excel file.

Next, a dialog box will open. Select Location and specify file name then click on Save button to save file.

Step 8: Now open file, and you can see all the measures.

You can follow same steps for exporting available calculated columns list  for that you just need to change the DMV.

In search box type “Columns” then double click on DMV: TMSCHEMA_COLUMNS.

Now modify the query as shown below.

select TableId, ExplicitName, Expression from $SYSTEM.TMSCHEMA_COLUMNS 
where [Type] = 2;

Next, you can save this data into Excel file following same steps as did earlier.

Apart that there are few DMV, which can be useful in preparing a documentation.

• Get the Calculated columns and Measure’s dependency for an object which are specified in DAX query.

SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY 
WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY [EXPRESSION] ASC

• Get the table objects in data model

select * from $SYSTEM.TMSCHEMA_TABLES

Also Read..

Power BI DAX

 9,655 total views,  1 views today