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

Accepted Solutions

Re: Determine which measures are used in a report

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
Super User IV
Super User IV

Re: Determine which measures are used in a report

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.


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

Not the Power BI thought police...

I have 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 Datanaut!

RMDNA Solution Sage
Solution Sage

Re: Determine which measures are used in a report

@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

Re: Determine which measures are used in a report

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

 

 

Super User IV
Super User IV

Re: Determine which measures are used in a report

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.


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

Not the Power BI thought police...

I have 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 Datanaut!

Sean Community Champion
Community Champion

Re: Determine which measures are used in a report

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

 

MPR
Frequent Visitor

Re: Determine which measures are used in a report

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

Re: Determine which measures are used in a report

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.

Re: Determine which measures are used in a report

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

Re: Determine which measures are used in a report

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors