cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ghuiles Regular Visitor
Regular Visitor

Is there a way to retrieve all measures?

Hello

 

Is there a way to retrieve all measures from one pbix file and export or copy them to a txt file?

That would help for further documentation and as well mass changes if needed.

Cheers.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

Hi @Ghuiles,

 

Yes there is.  You can run a DMV query against the Tabular cube.

 

Essentially you need to connect to your Power BI Desktop instance from a tool such AS Sql Server Management Studio (or MDX Studio) and issue the following Query

 

SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 
This will output information on your measures

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Ghuiles Regular Visitor
Regular Visitor

Re: Is there a way to retrieve all measures?

Hello 

 

I found a better solution, following that video: https://www.youtube.com/watch?v=rYlIp1PrBcA

 

2017-04-03 19_25_13-DaxStudio - 2.6.0.png

 

Basically, in the DAX Studio, you click on MDSCHEMA_MEASURES.

You get an SQL, execute it and you got a lot of details on your measures. 

That you can of course export directly to Excel.

 

Cheers

15 REPLIES 15
Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

Hi @Ghuiles,

 

Yes there is.  You can run a DMV query against the Tabular cube.

 

Essentially you need to connect to your Power BI Desktop instance from a tool such AS Sql Server Management Studio (or MDX Studio) and issue the following Query

 

SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 
This will output information on your measures

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sean Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

@Ghuiles

You'll have to use DAX Studio http://daxstudio.codeplex.com/

 

How to do it is described here http://exceleratorbi.com.au/getting-started-dax-studio/

 

Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

 

I think I'll have to find a better DMV for you too.  

 

The one provided only returns 1 row against a PBI Desktop model.  However it works beautifully against Tabular cubes.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

If you run the following DMV query, it shows a list of the various meta data tables you can query.

 

SELECT * FROM $System.DBSchema_Tables   

So for a list of Columns 

SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS where Table_Schema = 'Model'

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

Hi @Sean.

 

I didn't realise you could use DAX Studio for DMV's but I guess that makes sence.  I normally do my DMV work in SSMS, (or in Power BI Desktop itself) and that is a great article by @MattAllington

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sean Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

Yes the other thing I recommend is the Power Pivot Utilities Tool https://www.sqlbi.com/tools/power-pivot-utilities/

 

However whether you install the PP Util or not => start DAX Studio from EXCEL even if you just install the DAX Studio Add-In

 

This way when you are getting the List of ALL Measures in your Model you'll get the option to Output in Excel

If you don't start from within Excel you won't get that Option!

 

DAX Studio Excel.png

 

 

Ghuiles Regular Visitor
Regular Visitor

Re: Is there a way to retrieve all measures?

Hi guys!

 

Many Thanks @Sean and @Phil_Seamark! I learned a lot!

 

I used the SQL from Phil with the DAX studio. 

Currently we are not using an SQL server. The data for PBI comes from Excel (Essbase). 

 

Now i have the list of the measures, yes, but not their respective calculation.  Is there a way to get them?
This would really help me to make mass changes.

 

Cheers

Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

I haven't seen a DMV for this, but the PBIX file is just a .zip file so you could possibly parse the raw text (would be pretty ugly but you could do it in Power BI Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Ghuiles Regular Visitor
Regular Visitor

Re: Is there a way to retrieve all measures?

Thanks again Phil!

I did the work manually.

Cheers

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 258 members 2,582 guests
Please welcome our newest community members: