cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Determine which measures are used in a report

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

1 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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

13 REPLIES 13
Solution Sage
Solution Sage

@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

Community Champion
Community Champion

Method 1

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

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! Smiley Happy

 

 

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

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

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

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

 

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.

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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

Good to know.  Thanks Matt.  

 

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

@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! Smiley Happy

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

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

Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors