A very common scenario in Power BI is of extracting measure into text file and then loading them back again in the Power BI file so that you can create a page in the report that will show the content of each measure.
In this blog let's see 2 different ways of extracting the measure in your Tabular models into Excel or Text file.
Using DAX Studio
DAX Studio is probably the easiest way of doing this as you can run a DMV and then simply extract the content let's see how:
Open an instance of DAX Studio and connect to any tabular model either Power BI, Power Pivot, SSAS or Power BI Service.
In DAX Studio you need to go to the DMV pane and then search for measure, once you find MDSCHEMA_MEASURES you can drag and drop it on the right hand side.
To execute the code just press F5 or the Run button and then it will display all the measures and associated properties in the Results pane.
A lot of information that you see may be useless and not of any interest to you so if you want to limit the information you can simply reduce the columns that you see with the DMV, you just need to remove the aestrick and replace it with explicit column names.
And now to get the content of the measure in a text or Excel file you can go to the Output option and then select from the list of available options.
Let's go with Excel Static as that gives us nicely formatted table. All you need to do is select that option and then hit run once again and then DAX Studio will give you an option of selecting the path where you want to save the file.
Just save the file somewhere and then you can open the file and you will get a nicely formatted table:
Using Tabular Editor
Another option is of using Tabular Editor where you can create a C# code and then use it to iterate over the tables and measures collection let's see how.
It is upto you whether you want to use Tabular Editor 2 or Tabular Editor 3, for this blog I am going to use Tabular Editor 2 only because it is a free tool and is available for all.
First, we need to launch tabular editor and connect it to the instance of the Tabular Model. Then in the Advanced Scripting option we will begin to write our C# code
First we will create a loop that will iterate over all the table object in the tables collection by using this code:
As a first example we are going to print the output as the names of all the tables delimited by a new line character by using this code:
The above code will open a new window that will show the result as this:
Now that we know what we are iterating on we can start another nested loop to iterate over each measure in the tables that we have by using this code:
This is the result you will get which contains the names of all the measures.
To get the code of a measure you can replace m.DaxObjectName with m.Expression
with m.Expression you get:
Now all that needs to be done is to save the output in a text file. for that we can use the SaveFile method at the end.
This is how the updated code looks like:
Once you run the code it will save the file at the path that you have provided and this is how the content of the file will look like:
From the above image you can notice that it is not possible to identify to which measure a piece of DAX code belongs, to sort that we can concatenate the name of the measure with some kind of delimeter so that the name of the measure is easily visible to the reader and for that we can use this piece of code:
Updated code:
This is how the output of the code looks like in the text file:
So in this blog we saw 2 ways of extracting the content of measures from our Tabular models by either using DAX Studio or Tabular Editor, while DAX Studio is the quick and easiest way of getting the work done, Tabular Editor offers much more power to the developer to extract and modify how the final result will look like.