Determine which measures are used in a report

[MPR]

Hello,

I commonly find myself in a situation where I build many measures but not all of them end up in any visualizations.  This is common when I am trying to get a measure to work and I try different methods and forget to delete the ones that didn't work.

Does anyone know of an ease way to know which measures are used in a pbix report?  I would like to easily know which ones I can delete.

As always, I appreciate the help.

Mark

[Ayyappa_Pro]

Have a look at this cool tool from powerbi.tips . I has helped me a lot.

https://powerbi.tips/product/field-finder-tool/

[RMDNA]

@MPR,

Using DAX Studio, you can use the following commands to get a list of all measures and/or calculated columns in a report. There might be a way to pull if/where they're being used. I'll reinstall the program and check.

Pull all measures from PBI schema:

select * from $SYSTEM.TMSCHEMA_MEASURES

Pull all calculated columns (including system columns) from PBI schema:

select * from $SYSTEM.TMSCHEMA_COLUMNS where [Type]  = 2

[Sean]

Method 1

I have not tried this however being that its from @ImkeF I trust it works! 

https://www.thebiccountant.com/2015/12/18/visualize-dependencies-between-your-dax-measures/

Method 2

This is from @MattAllington (Matt I tried the file from 10/27/2017 but it did not work?)

Getting this message - "You must have exactly 1 instance of PBI Desktop open" which I do

I recall I tried the 1st version of this workbook some time ago and it worked but I don't think it had the dependencies then.

https://exceleratorbi.com.au/measure-dependencies-power-bi/

Method 3 (Have not tried this)

https://blog.datavizioner.com/power-bi-documenter/power-bi-documenter-august-2018-release/

@MattAllingtoncould you shed some more light on which DMV's you are referencing in the blog post above so we can possibly do this in DAX Studio?

Thanks!

[Greg_Deckler]

Method 1 and 2 seem like a ton of work. Method 3, I was looking at the app, it specifies PBIT files and has a limit of 2 MB.

[Sean]

@Greg_Deckler

I agree I wish they would incorporate this functionality in Dax Studio so we could

List Measures, Columns and Dependencies with the click of a button or two...

Actually @MattAllington's workbook is not that much work at all

Hopefully he'll respond as to why the workbook is not working?

Matt I'm using the desktop version not from app store!

I like to be able to revert to an older version just in case they break something and are taking days to fix.

Which has not happened recently but did beginning of 2018 couple times...

[MattAllington]

The error you are seeing is a common error and it is covered in the readme.txt file I included in the download.  When each instance of Power BI Desktop runs it creates a new copy of Analysis Services running in the background.  If there is more than one, then my workbook doesn't know which one you intend to connect to.  As covered in the readme.txt file, you should switch to the connection sheet, click open folder and then delete any old instances of AS that are not being used.  Of course if you have more than one instance open, then close all but one.

[MPR]

Sorry @MattAllington.  I did not read all of the readme file.  Clearing the other temporary files fixed the error.  

So using that Excel sheet, is there a way to see what measures or calculated columns are being used in one of the visualizations in the pbix file?  I watched your video on how to use the Excel file and wasn't able to understand that.

Thanks for your help.

[MattAllington · Accepted solution]

No, sorry.  You can't tell which measures are used in visuals using this approach.  I am not aware of a tool that can do this, however I think Power Pivot Utilities will show you which columns are not being used. Power Pivot Utilities  But this only works for Power Pivot, not Power BI

[Anonymous]

@MattAllington , Can you suggest me which system table stores the execution date ,time and duration  of measures?

Most probably there might be the system table to track these things as Power BI recently added as very extra ordinary feature called "Performance Analyser".

It we know that system table then we can categorize the active measures and passive measures and remove the passive measures from the report for optimization.

[MPR]

Good to know.  Thanks Matt.  

Great tool.  Thanks for releasing it for everyone to enjoy.

[Sean]

@MattAllington

Thanks for responding!

Yes guilty as charged - I did not read the included text file either. (I opened it but just glanced over...)

I’ll give this a try tomorrow.

Thank You! 

[MattAllington]

I have just updated the workbook so it is clearer what causes this error and to provide embedded instructions on how to fix it.  Give it another try.

[MPR]

I wasn't able to get @MattAllington's workbook to work either.  It looks like it would be a great solution.

[Greg_Deckler]

Boy, if you find a way to do this easily let me know!! I am not aware of a way to do this currently but boy would it be nice.