THE SELF-SERVICE-BI BLOG

Wir lieben Microsoft Power BI

Technical Topics (english), Tools /

How to identify Measures not used in your pbix file

WARINING: Due to a broken DMV in Power BI Desktop, that I use for this solution, we cannot use this tool anymore, until until Microsoft fixes the issue. Sorry 😐

When I work with customers‘ pbix files, I often deal with files with several hundred measures. At the beginning I like to get an overview which of these measures might not be used at all and could possibly be removed. For this purpose, I have developed a method to locate these measures, which I would like to share in this article. Warning: Please note that this method does not consider the measures used in the Filter Pane! So before you delete a seemingly unused measure, please check if it was used in the Pane filter. 

You can download my report file at the end of this post. The download does not require a password. If you are still asked for your OneDrive password, change your browser to Edge.

The definition of unused measures

Unused Measures are Measures that are neither used in any visualization in the .pbix file nor referenced in other Measures, calculated columns or calculated tables! So how can I identify those Measures? Let’s find the answer together…

Gather the necessary information

To find the unused measures, I need the following information:

  • A list of all existing measures in the .pbix file,
  • A list of the measures used in the visualizations within the .pbix file
  • A list of measures referenced by other measures, calculated columns and/or calculated tables.

The logic is the following: When I remove from the all measures list those measures that are used in visualizations, I have those measures that are not used in visualizations. But they can still be referenced in other calculations (and therefore cannot be removed). So, if I hold against the list of all referencing calculations here, I finally get the measures that are not used within the .pbix file.

Retrieve a list of all measures

Since Power BI Desktop runs an instance of SQL Server Analysis Services Tabular in the background, I can use a DMV (Data Management View) to get information about my data model. You can use Power Query to gather this information using the following single line of code:

= OleDb.Query("Provider=MSOLAP.8;Data Source=localhost:61200;Update Isolation Level=2", "SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES")

The result looks like this:

Using the DMV SYSTEM.MDSCHEMA_MEASURES to get information about all measures, Power BI, Power Query
Using the DMV SYSTEM.MDSCHEMA_MEASURES to get information about all measures

Be aware, that the number 61200 has to be changed in your code, as this is the process ID of the instance of Analysis Services running on your machine. The easiest way to find this number on your machine is to run DAX Studio (download it here) and connect it to your (already opened) pbix file. When you are connected, you find the desired process number in the bottom line, as you can see here:

finding the process ID of your Analysis Services instance behind Power BI Desktop
finding the process ID of your Analysis Services instance behind Power BI Desktop

Now that we have a list of all measures, let’s find those measures, that are used in report pages.

Retrieve a list of Measures, used in report pages in Power BI Desktop

Since May 2019 Power BI Desktop has been equipped with the extremely useful „Performance Analyzer„. This tool helps to find starting points for the optimization of slow reports. But this tool also helps me to identify those measures that are actually used on report pages. Follow these steps:

  1. Create a blank page
  2. Go to View → Performance Analzer and make the pane visible
  3. Click on Start recording
  4. Then select each page, one after another and wait until the visuals are fully loaded
  5. Press Stop
  6. Export the JSON file
Use the Performance Analyzer, to extract DAX queries from your pbix file, Power BI Desktop
Use the Performance Analyzer, to extract DAX queries from your pbix file

The JSON file contains all DAX queries, generated to update the visuals when selecting the page. If a measure is used in a visual, it is represented in the DAX query and I can extract that information from the JSON file. Of course I use Power Query to do that job for me and the result looks like this:

List of all measures, used in at least one visualization in the pbix file, Power BI Desktop
List of all measures, used in at least one visualization in the pbix file

Intermediate result

With the retrieval of all measures and the JSON file, with all measures used in visuals, I can now make a comparison and determine which measures are not used in visuals. So far, so good. However, just because a measure is not used directly in a visual, it is not automatically unused. It can be referenced in other measures, calculated columns, or calculated tables. Therefore I would like to check whether these now determined measures are really completely unused before I decide how I want to proceed with them…

Retrieve a list of calculation dependencies

The DMV, which I used for the retrieval of all existing measures, is by far not the only one available. Another useful DMV I will use to determine the measures referenced by other measures, calculated columns, or calculated tables is the following: $SYSTEM.discover_calc_dependency (see documentation of this DMV here).

I can again use Power Query to retrieve the results of this DMV like so:

= OleDb.Query("Provider=MSOLAP.8;Data Source=localhost:61200;Update Isolation Level=2", "SELECT * FROM $SYSTEM.discover_calc_dependency")

Again, the process id 61200 has to be changed to find the Analysis Services instance on your machine.

The result looks like this:

Using the DMV SYSTEM.MDSCHEMA_MEASURES to get information about all measures, Power BI Desktop, Power Query
Using the DMV SYSTEM.discover_calc_dependency to get information about calculation dependencies

This DMV returns a clear picture of the interdependencies of calculations. For me this DMV answers the question in which measures, calculated columns and/or calculated tables my measure is referenced. If a measure is not used in any visualization and does not appear in the calculation dependencies, it is unused.

The final table

With all this information, I can now create the following table, which tells me whether a measure is used in report pages and whether it is referenced in any way:

The final query: Which measure is not used at all in the pbix file, Power BI Desktop, Power Query
The final query: Which measure is not used at all in the pbix file

The screenshot shows in line 5 that the measure _Distinct_Medals is not used in visuals, but is at least referenced by other measures, calculated columns and/or tables. The measure „Not_used_anywhere“ is neither referenced nor used in visuals.

Download and usage of my Measure Analysis pbix file

I loaded all the described data from DMVs and the JSON export file into a pbix file and added a (very small, but helpful) report. Admittedly, I didn’t take any trouble with the optical design. I’ll leave that to you 😉

You can download the report file here.

Open the file and edit two parameters:

  • the process ID of the Analysis Services instance
  • the path and name of the exported JSON file (Make sure that your string does not contain quotation marks at the beginning and at the end)
Edit parameters, Power BI Desktop
Edit parameters
Enter new parameters, Power BI Desktop, Power Query
Enter new parameters

After you have clicked OK, you will see the following message in the upper part of the report page:

Click on „Apply changes“ and click on „Run“ in the following dialog, to allow the run of the DMV MDSCHEMA_MEASURES:

Run DMV MDSCHEMA_MEASURES, Power BI Desktop
Run DMV MDSCHEMA_MEASURES

Then you will be asked to enter your credentials. Use „Windows“ and „Use my current credentials“:

Use Windows credentials to connect to the instance of Analysis Services, Power BI Desktop
Use Windows credentials to connect to the instance of Analysis Services

After you have entered your credentials, Power Query prompts you to determine the privacy levels for the data source. Since in this particular case the communication between this PBIX file and the Analysis Services instance is behind the file to be analyzed, you can safely click on „Ignore Privacy Levels…“:

Ignore Privacy Levels and connect to pbix file, Power BI Desktop
Ignore Privacy Levels and connect to pbix file

Click on „Apply changes“ and click on „Run“ in the following dialog, to allow the run of the DMV :

Run DMV dicover_calc_dependency, Power BI Desktop
Run DMV dicover_calc_dependency

After the model refreshed, you can use the following report:

The final report: Find the unused meaures, Power BI Desktop, Power BI
The final report: Find the unused meaures

Here is a short description of the report:

  • Column1: Table name and Measure name.
  • Column2: If the measure was stored in a folder, you can read it here.
  • Column3: DAX-Expression of the measure
  • Column4: Is the measure used in a visual, somewhere in this pbix file?
  • Column5: Is this measure referenced by any measure, calculated column/ table in this pbix file?

I hope that this manual will help you to bring even more order into your data models 🙂

Greetings from Germany,

Lars

Leser-Interaktionen

Kommentare

  1. Raymond meint

    Wow, very nice.

    Its still working also in the latest Power bi version

    How can i make overview for each measure which visual is used on which report is that possible?

    • Henrik Vestergaard meint

      Is the solution working in spite of the foreword from Mr. Schriber: „WARINING: Due to a broken DMV in Power BI Desktop, that I use for this solution, we cannot use this tool anymore, until until Microsoft fixes the issue. Sorry“ ?
      Regards, Henrik?

  5. Kadir meint

    In powerbi i have one page with over 40 visuals which appears and disappears with like 50 bookmarks. How will i get all the json files?

  6. Shhram Es meint

    You can refactor the queries in the report as follows to get them to work. I created a parameter called „Database“ that you can find by running a DMV query in DAX Studio — see comment below.

    let
    // to get database, run this in DAX Studio: SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS
    Source = AnalysisServices.Database(
    „localhost:“ & Number.ToText(ProcessID),
    Database,
    [Query=“SELECT * FROM $SYSTEM.discover_calc_dependency“]),
    #“Geänderter Typ1″ = Table.TransformColumnTypes(Source,{{„DATABASE_NAME“, type text}, {„OBJECT_TYPE“, type text}, {„TABLE“, type text}, {„OBJECT“, type text}, {„EXPRESSION“, type text}, {„REFERENCED_OBJECT_TYPE“, type text}, {„REFERENCED_TABLE“, type text}, {„REFERENCED_OBJECT“, type text}, {„REFERENCED_EXPRESSION“, type text}, {„QUERY“, type text}}),
    #“Gefilterte Zeilen“ = Table.SelectRows(#“Geänderter Typ1″, each ([REFERENCED_OBJECT_TYPE] = „MEASURE“)),
    #“Sortierte Zeilen“ = Table.Sort(#“Gefilterte Zeilen“,{{„REFERENCED_OBJECT“, Order.Ascending}}),
    #“Entfernte Duplikate“ = Table.Distinct(#“Sortierte Zeilen“, {„REFERENCED_OBJECT“})
    in
    #“Entfernte Duplikate“

  9. Dom meint

    Hi Lars,

    do you know if there is a way to identify in which visual exactly a measure is used? By doing this you would be able to narrow down where you have used a specific measure. In case of a huge report that would be very helpful.

    Thanks in advanced.

    Cheers,
    Dom

    • Mijalis Méndiz Pazos meint

      Hello Dom,

      You can do that with Dax Studio, one by one yes, but still, it may solve your problems. You just have to open Dax Studio, search for the measure, right-click on it and select the option „Show objects that reference the measure“. It will print a table with all the objects and measures that use the one you are analyzing. It also works for columns.

      Best regards

      Mijalis

  10. Stefan meint

    So far the best thing I came across B-) your the best man, I one hour I have removed 24 unused measures in our model

  12. Jon Marquet meint

    I’m having an issue when trying to run in Power Query:

    = OleDb.Query(„Provider=MSOLAP.8;Data Source=localhost:62201;Update Isolation Level=2“, „SELECT * FROM $SYSTEM.discover_calc_dependency“)

    I get the following error message:

    DataSource.Error: OLE DB: Se ha detectado un nodo de dependencia no admitido.
    Details:
    DataSourceKind=OleDb
    DataSourcePath=data source=localhost:62201;provider=MSOLAP.8;update isolation level=2
    Message=Se ha detectado un nodo de dependencia no admitido.
    ErrorCode=-2147467259

    I have no idea how to solve it. It’s something about the dependency node, but i have no clue how it works.

    Any help would be much apreciated.
    Thank you.

    • Lars Schreiber meint

      Hi Jon,

      I haven’t used the tool myself for a while. The hard truth is, that the DMV ‚discover_calc_dependency‘ which is queried in the M statement above, is broken and cannot be queried anymore from Power BI Desktop. This is no issue of my tool, but an issue of Power BI Desktop. You will get the same problem in DAX Studio, when you try to query this DMV. Due to this issue, my tool cannot be used until the broken DMV will work again 🙁

      Thanks,
      Lars

      • Jon Marquet meint

        Hi Lars.

        Thank you very much for your answer and for your effort.
        Let´s see if Microsoft solves the issue as soon as possible and the tool works again. It’s a really usefull tool.

        Thanks again,

        Jon

  13. Nathany meint

    Hi there, it’s me again, I’ve just posted in the comments about measures names in the json files, and I downloaded your pbix and could finally understand the steps that you used to get those names. Thank you!!!!!!!

  14. Nathany meint

    Hi there, first of all, thank you for share this content, it’s awesome! Could you share how did you get the measures in the json file? I exported the json file normally, but when I put it into power quary editor I could not find the measures names. 🙁

  15. Charaf meint

    Very nice blog Lars,

    Can you share the path and name of the exported JSON file?
    I cant see the full path in the picture?

    Kind Regards,
    Charaf
    From the Netherlands

    • Lars Schreiber meint

      Hi Charaf,

      thanks for your feedback. You don’t need to see MY path and MY name of the JSON file. It needs to be the path, where you saved YOUR JSON file and the name YOU specified. Makes sense?!

      Thanks,
      Lars

      • Charaf meint

        Thanks for your quick answer,

        I mean of the example?
        i did make my own example but didnt see the measures in the json. So i try your example but i dont have the right json export for the example.

  17. cristina meint

    Nice article.. could be possible to just include any column that has been used in the visuals?
    In that way we can decide if that column or column really can be remove or stay.

  18. Cody meint

    Hi Lars,

    Super tool!
    For most of the pbix files it works great, however last I get an OLE DB error.
    The pbix. file contain a dataflow as source, could this be the reason I got the error??
    And how to solve it?

  21. Sam Benson meint

    With many thanks!!

    I have been looking for something that will help remove all the redundant and duplicate measures that I don’t need.

  22. Denis HAVE meint

    Hi Lars,
    I have a problem in reading JSON file, i cannot find howto to arrived to the list of ‚Used_Measures_In_Visuals‘.
    Please can you help me to transform the powerBIperformance.json into table with only used measures.
    Thanks a lot for your help.

  23. Denis HAVE meint

    Hi Lars,
    I have a problem in reading JSON file, i cannot find howto to arrived to the list of ‚Used_Measures_In_Visuals‘.
    Please can you help me

  24. Sri meint

    Hi, Very valuable article. However, I use live connect(SSAS tabular model) as our source to Power BI and hence not able to do a lot of things using your pbix. I was wondering how you were able to just get the measures name from the performance analyzers json file ? I am trying to see what measures are used across all our pbix files and get rid of old/unused measures.

    I also get 0 rows when I run the query SELECT * FROM $SYSTEM.discover_calc_dependency in DAX studio. Do you know why this happens?

    Appreciate your help.
    Thanks,
    SN

  25. Fredrik Vestin meint

    Lars!

    This is fraking awesome! Thanks for providing such a nice tool together with very clear instructions for usage!

    • Lars Schreiber meint

      Hi Andrey,

      thanks for your feedback and question. Yes, it is possible to also include (calculated) columns/ tables, but if a column/ table is not used in any measure, it doesn’t mean, that it is not used att all. Think of bridge tables. It is likely, that they will not appear in a measure formula, but they are necessary for the data model. This is why I did not include them in my solution, because it would/ could lead to wrong conclusions.

      Does that make sense?

      Thanks and cheers,
      Lars

      • Steffen meint

        Nice solution.

        It would be useful to include columns anyway. If they are used in relations, they can be identified using SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS

  27. Upen meint

    Hi Lars,

    Could you suggest how do I replace Process Id in you power bi file to create connection and use the extracted json file?
    Much appreciated for the help.

    Kind regards,
    Upen

    • Lars Schreiber meint

      Hi Upen,

      I was not able to replace the process id and get access to the SSAS tabular instance. This is why I suggested connect to SSAS tabular directly using the code from my last reply.

      Cheers,
      Lars

  28. Upen meint

    Hi Lars,

    Thanks for posting the wonderful article to categorize used and unused filters.
    I tried for the power bi file which has got the live connection. I couldn’t use DAX studio to get Process Id. Could you please suggest me how do I capture the Process Id for the live connection file mode? I opened the task manager to get Process Id for the power BI file but it didn’t work.
    Your suggestion is much appreciated.

    Best regards,
    Upen

    • Lars Schreiber meint

      Hi Upen,

      thanks for your feedback 🙂

      In case of live connection to a SSAS tabular model, I guess it is best to not rely on the process id (the process in task manager is ‚msmdsrv.exe‘), but to create a connection to the SSAS model directly, like so:

      = AnalysisServices.Database(Server like „334BA86J\SSASTABULAR“, database like „AdWorks“, query the specific DMV like so [Query=“SELECT * FROM $SYSTEM.discover_calc_dependency“, Implementation=“2.0″])

      Hope that helps you out 🙂

      Cheers,
      Lars

      • Mika Kouvo meint

        Hi Lars and Upen,

        Great stuff!!! In case of live connection [Query=“select * from $SYSTEM.MDSCHEMA_MEASURES“,Implementation=“4.0″] (probably also with other implementations) will do the job. I used it successfully when connecting live PBI model in Premium workspace.

        Cheers,
        Mika

      • NATHANY DE ALMEIDA MIGUEL meint

        Hi Lars,
        My current dash has a live connection to a dataset published in the PowerBI Service. In order to to what you suggested in these cases (create a connection to the SSAS model directly), how I supposed to find the server name of a dataset published in the PowerBI Service? Or first of all, is it possible? Sorry for the question, I am noob at SSAS.
        Thanks in advance!
        Greetings from Brazil
        Nathany

        • Kai meint

          Hi Nathany, Lars,

          I would also be interested if it is possible to find out unused measures for the setup where I have a seperate dataset .pbix w/o visuals and stand-alone .pbix files for visualizations / reports with a live connection to the published dataset in Power BI Serivce.

          Any suggestions?

          Regards