Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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: Is...
Need Help
  • foodd's avatar
    foodd
    3 years ago

    Anonymous, 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.