Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
Proud to be a Datanaut!
Wow! Awesome. Thank you.
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
Proud to be a Datanaut!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
61 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |