One thing that often happens is when users are using a dataset, they want to know which measures are available. And not only that sometimes they want to know the measure definition.

This got me thinking and how best could I give this to the users in my organization to be able to find this information quickly and easily.

In the past this was a manual effort not only to export the measures, but also to maintain a document, so that as measures are added, updated, or deleted I would then need to manually update some document.

Yep, you guessed it I created a Power BI report which has got all the measures and their measure definitions, which will update with the dataset! And I show you how I did this below.

Connecting to my PPU/Premium Dataset

The first steps were for me to connect to the PPU/Premium dataset via the XMLA Endpoint.

This allowed me to be able to define the query I wanted to run.

  • I clicked on Get Data and selected “SQL Server Analysis Services”
  • I then put in my PPU/Premium App Workspace URL
    • EG: powerbi://api.powerbi.com/v1.0/myorg/PPU%20Space%20Tests
  • I then put in my dataset name
    • EG: Exact Aggs – PPU
  • I then change the data source to be “Import”
    • This allowed me to then specify an MDX or DAX query
  • In the MDX or DAX Query I think put in the following DMV

    select * from $SYSTEM.MDSCHEMA_MEASURES

  • This is what it looks like below (NOTE: I have changed my PBIX file to use parameters)
  • I then clicked Ok, the first time I connected I had to log in and I chose “Microsoft account”
  • I then loaded the data into the Power Query Editor
  • I then copied the above table but changed it to use another DMV, which had slightly different properties that I wanted to capture
  • I then shaped the data and got it into the format that I required.
  • Finally, I loaded it to my dataset.

Creating the Power BI Report

I then created the Power BI Report as shown below.

I created a slicer with the search functionality, so that users could search for the measure.

I also created the last time the dataset has been refreshed to know how up to date the measures are.

Finally, I also added in the Measure Modified date to know when last it was updated.

Summary

In summary I have shown you how I connected to my dataset and got all the measures and their definitions.

Here is the link to the PBIT file that I created: PPU – Measure Definitions.pbit

If there are any comments, please let me know.

Thanks for reading!