cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ghuiles
Advocate IV
Advocate IV

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
Phil_Seamark
Microsoft
Microsoft

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!

View solution in original post

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

View solution in original post

19 REPLIES 19
JasonPad
New Member

With May 2021 Power BI Desktop,

 

 try this

https://github.com/JasonPad19/PowerBIScanner/blob/main/README.md

Anonymous
Not applicable

Is there any way I can retrieve the measures and dimensions of a particular chart?

jsh121988
Microsoft
Microsoft

This is the first result in google, so I'm posting here.

 

Today I discovered that the old method of unzipping a pbix file and exploring DataModelSchema works on PowerBI Template .pbit files.

 

  1. Open a .pbix file in PowerBI Desktop
  2. Save as Template .pbit
  3. Change the file extension to from .pbit to .zip
  4. Open DataModelSchema in a text editor.
  5. Extract the schema

Measures can be found in: model.tables.measures

Columns can be found in: model.tables.columns

Calculated Columns can be found in: model.tables.columns by filtering calculated = true

 

To streamline this, I've written a program in C#, but any language that parses JSON will work. Here is the basic C# source code. I just used linqpad to run it.

// using System;
// using System.Linq;
// using System.IO;
// using System.Collections.Generic;
// using Newtonsoft.Json

void Main()
{
	// get the JSON from pbit DataModelSchema
	string strFolderName = @"c:\path\to\file\";
	string strFileName =  @"DataModelSchema";
	string strJson = File.ReadAllText(strFolderName + strFileName);

	// Convert the Json to a dynamic object
	dynamic oJson = JsonConvert.DeserializeObject<dynamic>(strJson);

	// Extract the tables to a list<dynamic>
	List<dynamic> oTables = new List<dynamic>(oJson.model.tables);

	// Remove hidden tables from the pull. These are only sysetm generated date tables.
	oTables = oTables.Where(t => t.isHidden != true).ToList();

	foreach (var oTable in oTables) // Foreach Table, do something
	{
		Console.Write((string)oTable.name); // Output the table name
		if (oTable.measures != null) // If the table has measures
		{
			List<dynamic> oMeasures = new List<dynamic>(oTable.measures);
			foreach (var oMeasure in oMeasures) // Foreach measure
			{
				Console.Write(string.Format("\n\n\t\t{0} = {1}", (string)oMeasure.name, (string)oMeasure.expression)); // Write the measure and expression
			}
		}
		if (oTable.columns != null) // If the table has columns
		{
			Console.WriteLine(); // Insert a new line
			List<dynamic> oColumns = new List<dynamic>(oTable.columns);
			oColumns = oColumns.Where(c => c.type == "calculated").ToList(); // Filter on calculated columns only, but this could be removed for all
			foreach (var oColumn in oColumns)
			{
				Console.Write(string.Format("\n\n\t\t{0} = {1}", (string)oColumn.name, (string)oColumn.expression)); // Write the column and expression
			}
		}
		Console.WriteLine();
	}
}

 

grahamfletcher1
Regular Visitor

Is there a way to do the reverse of this? I want to load the measures into the PBIX file from SQL.

 

I'm thinking of creating a measure library in SQL that would give all the different developers access to the list of measures. It also means measures can be edited and updated across multiple PBIX files.

Sean
Community Champion
Community Champion

@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/

 

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!


@Phil_Seamark wrote:

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

 

 


@Phil_Seamark

Could you have a further explanation on how to do that in Power BI Desktop. I know the DMVs in SQL Server, Just wondering how to do that in Power BI Desktop. Thanks in advance.

Hmmm, maybe not.

 

You used to be able to unzip Power Pivot models and suck the formulas out of the xml files, but they have compressed/encrypted the data so not so straight forward.  


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

Proud to be a Datanaut!

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

Anonymous
Not applicable

Any idea how to obtain the measures that were created in a Power BI report and the Power BI report connects to an Analysis Services database in Direct Query mode?

KevinNC
Frequent Visitor

That's great! Thanks for sharing

Sean
Community Champion
Community Champion

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

 

 

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

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 🙂


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

Proud to be a Datanaut!

Thanks again Phil!

I did the work manually.

Cheers

 

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!

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!

Beautiful 🙂

Phil_Seamark
Microsoft
Microsoft

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!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!