Column or Measure used in a formula

[ConnieMaldonado]

I took over a report from a colleague laid off due to Covid.  The report is pretty extensive with over 100 columns and measures.  Is there anyway to determine if a measure or column is used in a formula anywhere in the report?  Is there a way to export all the formulas?  Thanks!

[ConnieMaldonado]

Wow!  Awesome.  Thank you.

[BA_Pete · Accepted solution]

Hi @ConnieMaldonado ,

Export all formulas:

1) Get yourself DAX Studio from here: https://daxstudio.org/

2) Once you connect to a PBIX model using DAX Studio, you will be able to see your 'localhost' number at the bottom right of the app window.

3) Use this localhost number in the following M within a new query in your PBIX (where you can currently see 57131):

= OleDb.Query("Provider=MSOLAP.8;Data Source=localhost:57131;Update Isolation Level=2",
"SELECT * 
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE MEASURE_AGGREGATOR = 0")

This will give you the full measure output of your PBIX.

For just Table Name, Measure Name, Measure DAX output, use this M (remember to swap your localhost number):

= OleDb.Query("Provider=MSOLAP.8;Data Source=localhost:53931;Update Isolation Level=2",
"select 
   MEASUREGROUP_NAME, 
   MEASURE_NAME, 
   EXPRESSION
from $SYSTEM.MDSCHEMA_MEASURES
where MEASURE_AGGREGATOR = 0")

Find if/where measures/columns are used:

1) Get yourself a copy of @ImkeF 's PBI Cleaner Tool from here: https://www.thebiccountant.com/2020/01/01/tidy-up-power-bi-models-with-the-power-bi-cleaner-tool/

The download link is right at the bottom, but the page also includes super-useful instructions.

2) Follow setup instructions and enjoy!

Both the items I've recommended here (DAX Studio, PBI Cleaner) have far broader applications than your use-case, so I would recommend spending some time with them, but they are also the best things to quickly get done what you need to get done.

Pete