Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tyatsenko
Frequent Visitor

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

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:

ModelViewProperties.png

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!

1 ACCEPTED SOLUTION
foodd
Super User
Super User

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

View solution in original post

5 REPLIES 5
tyatsenko
Frequent Visitor

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

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

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.

tyatsenko_0-1696595432278.png

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

 

tyatsenko_2-1696595965870.png

  • 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
Super User
Super User

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors