cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Moderator Eric_Zhang
Moderator

Re: Is there a way to retrieve all measures?


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

Phil_Seamark Super Contributor
Super Contributor

Re: Is there a way to retrieve all measures?

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!

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

View solution in original post

grahamfletcher1 Frequent Visitor
Frequent Visitor

Re: Is there a way to retrieve all measures?

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.

KevinNC Frequent Visitor
Frequent Visitor

Re: Is there a way to retrieve all measures?

That's great! Thanks for sharing

Highlighted
jsh121988 Regular Visitor
Regular Visitor

Re: Is there a way to retrieve all measures?

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();
	}
}

 

Sushantjb70 Frequent Visitor
Frequent Visitor

Re: Is there a way to retrieve all measures?

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 359 members 3,172 guests
Please welcome our newest community members: