Forum Discussion

jadewind's avatar
jadewind
Advocate I
8 years ago
Solved

calculated columns table

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 docume...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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?)

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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