calculated columns table

[jadewind]

Hi everyone,

Is it possible to create a table in Power BI to show all the calculated columns / measures with their DAX formulas displayed in the table? I just thought it would be easier to document my work instead of copying and pasting each calculated column/measure's name and DAX into a spreadsheet...

Thanks in advance for any advice!

[Phil_Seamark]

Hi @jadewind

Yes this is possible by running a DMV query against your model while it is loaded in Power BI Desktop.

In a nutshell, the fastest way to get your information is to run DAX Studio, which is a free install.  Once you download and run that, you can connect to your Power BI Desktop and run queries against it.

If you run the following queries individually you can configure the output to a file

select * from $SYSTEM.TMSCHEMA_MEASURES

select * from $SYSTEM.TMSCHEMA_COLUMNS

[campagnol54]

Hello ,

I personaly use these queries in DAX studio after connecting to the Power BI report:

//  Uncomment the query you need

//  Query to get columns :
//select [ExplicitName] as [Column name], [Expression] as [Formula] from $SYSTEM.TMSCHEMA_COLUMNS where [Type]=2 and [SystemFlags]=0

//  Query to get measures :
select [Name] as [Measure name],[Expression] as [Formula] from $SYSTEM.TMSCHEMA_MEASURES

[jadewind]

Thanks Phil_Seamark. Unfortunately I am not allowed to install anything on my computer. So I assume there is no inbuilt way to do it in Power BI?

[Phil_Seamark · Accepted solution]

You can do, but you need to find out the port number Power BI Desktop is sitting on it. 

To do this run the following from the command prompt

TASKLIST /FI “imagename eq msmdsrv.exe” /FI “sessionname eq console”

this should give you a table of data.

What we want is the PID from the list for msmdrv.exe

this will be a number between 1 and 999999

next use that number in the following command at the command prompt

netstat /ano | findstr “nnnnn”

replace the nnnnn with the number from step 1.  This gives you the current port number your Power BI Desktop is listening on.  It will change when you close and reopen the app.

Now, click the Get Data button and choose "Analysis Services"

In the ServerName type localhost:nnnnn

The nnnnn in this case should be the number from the 2nd column of the output of the netstat command.

This will allow you to connect to your data model.

You'll need to navigate through to the tables in order to get the GUID For the database name.

Once you have the database name you can re-connect to "Analysis Services" and this time enter both the Servername AND the Database name.  Here is where you can enter one of the DMV queries to give you details of your measures and columns.

The easier way is just to take your PBIX file to a machine that has DAX Studio (a home PC perhaps?)