Using model's metadata to create table

[Anonymous]

Hi all

One of the things that we thing will make life easier for our users is if we have a report page that says what measures are in the model and gives a brief description. Helpfully, Power BI supports a "description" attribute for each measure, and so the obvious thing to do is simply to query the model's own metadata to produce a table listing all measures and their description. What I can't figure out is how you can do this.

Help please!

Thanks

Stuart

[Anonymous]

Hi @Anonymous ,

Sorry for my late reply ... You could follow these steps.

1.Use the following Query code in DAX Studio(the pbix file should be run )

SELECT
[Measure_Name] as [Metric],[Measuregroup_Name] as [Table],[Description],[Expression]
from 
$SYSTEM.MDSCHEMA_MEASURES 

ORDER BY [Measure_Name];

2.Clike OutputàFile and Run again, the results will be save as Text file.

3.Then you could get data from the Text file

You could take a look at these two articles for more information.

https://insightsquest.com/2017/12/14/query-power-bi-field-descriptions/

https://exceleratorbi.com.au/getting-started-dax-studio/

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Anonymous]

Thanks all for the responses so far, but I've had limited success. @amitchandak  and @Fowmy , your links seem to be designed for a one-off exercise and rely out output files. @MFelix , your link got me further in that I was able to generate a table of measure directly in the Power BI report, but it relied of the PID of the open file, which changes:

let
    Source = OleDb.Query("Provider=MSOLAP.8;Data Source=localhost:52697;Update Isolation Level=2", "SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES")
in
    Source

What I erally need is:

1. a report in the Power BI service (app.powerbi.com)
2. that is able to generate a table containing measure metadata
3. which can then be reported to users in the form of a table visualisation.

[Fowmy]

@Anonymous 

One way to achieve this is by going to DAX Studio and run the following script, It will list all the measures in the model Then you can export it to ExcelCsv and proceed.

select * from$SYSTEM.TMSCHEMA_MEASURES

Dax Studio download: https://daxstudio.org/

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[MFelix]

Hi @Anonymous ,

In the blog post below you have a description of how to identify the not used measures however one of the first steps is to retrieve all the measures in your file.

Read the part Retrieve a list of all measures and you can get the expected result.

https://ssbi-blog.de/blog/technical-topics-english/how-to-identify-measures-not-used-in-your-pbix-fi...

[amitchandak]

@Anonymous , See if these can offer you some help

https://radacad.com/exposing-m-code-and-query-metadata-of-power-bi-pbix-file

https://www.finditez.com/blog/extract-power-bi-metadata/

https://cran.r-project.org/web/packages/pbixr/vignettes/explore.html