How did I keep my Power BI dataset measures documented and up to date?
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.
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!