How to use Power bi desktop model view Properties fields to build list of Definitions for App users

[tyatsenko]

Hi,

I would like to use "Description" field from "Properties" in Model view power bi dektop to build "List of Report Definitions" for report users, that consume report via Power Bi App:

Is it possible to genetate table from "Properties" data with following fields?

1. Table Name

2. Field Name

3. Field Description from Properties (highlighted in printscreen above)

The idea is to add this table as a new report page, lable as "Report Definitions" and make available to the users via power bi App.

Thanks!

[tyatsenko]

@foodd , 

Thanks! this is exactly what I was looking for! 

I will pull descriptions for measures and dimentional columns and create new report page with this info.

[foodd]

@tyatsenko Fantastic!  Would you mind sharing the PBIX, it would be of value who are putting documentation together.   

[tyatsenko]

Hi, 

Here is a brief explanation what was done.

1. In Power bi desktop > modeling view >Data I added Desctiption to all measures, some of the columns (the once I used as a dimentions, calculated columns).

2. In power bi desktop > External tools > open Dax Studio (I have verion 3.0.10).

3. In Dax Studio > 1.)open DMV > 2.)double click on TMSCHEMA_COLUMNS  (this will get columns metadata, for measures - use  TMSCHEMA_MEASURES 3.) this will genereate select statement. Click Run to see the result.

4. To bring this data back to Power Bi desktop - you need to copy 3 things from DAX studuio:

• Server Name - it starts with "localhost:" and is displayed in the bottom right corner of your Dax studio.

Model ID - from Metadata > right click on Training and copy Database id

• select statements for columns ( select * from $SYSTEM.TMSCHEMA_COLUMNS) and measures ( select * from $SYSTEM.TMSCHEMA_MEASURES)

5. In Power bi desktopo Get data >Analysis Services.

Fillup ServerName and Database(Model ID). Select Import mode and in DAX query enter select statement.

Authenificate as Windows - Use my current credentials. Lets call this query Metadata_Measures.

6. In Power Query create new blank query "Metadata_mirror_measures" from "Enter data", copy Metadata_Measures query in there.

Repeat for each metadata table.

7. Disable refresh and load of Metadata tables. Disable refresh of Metadata_mirror tables.

I did steps 6 and 7 so I dont need to refresh those tables in the power bi service.

8. I created Documentation page using  Measures and columns description data.

[foodd]

Chris Webb in his January 2020 Blog Post shows how to Analysing Power BI DMV Queries In Power BI Desktop

With this, you will be able to import the documentation data into a form as you are 

interested.

[foodd · Accepted solution]

@tyatsenko, you can use DAX Studio to retrieve this via Dynamic Management View (DMV).   I don't have the script handy, though, if you begin with these three DMVs as starting points:

• Retrieve GUID from Power BI Model:  SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS
• Retrieve Table ID:  select * from $SYSTEM.TMSCHEMA_TABLES
• Retrieve Table and Column Info:  Select * from $SYSTEM.TMSCHEMA_COLUMNS as a starting point

This can be extended to measures, etc.