cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MPR
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
RMDNA
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

Sean
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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Sean
Community Champion
Community Champion

@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

Anonymous
Not applicable

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

Sean
Community Champion
Community Champion

@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.
MPR
Frequent Visitor

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

Greg_Deckler
Super User
Super User

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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors