Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MPR
Advocate I
Advocate I

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

14 REPLIES 14
Ayyappa_Pro
New Member

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

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

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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 an 8 times 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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
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.

@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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.