Hi, it is easy to create a calculated measure and also to delete it if you do not need it anymore.
And when you use a an existing column in a powerpivot there is also automatically a measure created.
Example:
Sum of ColumnName
:=SUM('TableName'[ColumnName])
But when the column is taken out of the pivot this automatically created measure is not deleted. So if I list all measures also the not used automatically created measures get still listed, even though they are not used anymore.
I do this listing especially for files with many measures to keep an overview and also as a safety measure for an easier reconstruction in case pp gets unchangeable. In order not to bloat the list it would be good if these not used automatically created measures could be eliminated.
Hi Matthias,
What version of Excel do you have? I don't get this behaviour in Office 365.
Mynda
Hi Mynda,
I am non 2013. And the measures are listed by PP Utilities (List measures): https://www.sqlbi.com/tools/power-pivot-utilities/
Thanks,
Matthias
So when you say the measure is listed after being removed from the PivotTable, do you mean it is still listed in the PivotTable field list, or do you mean the PP Utility is still listing it in the static list it creates in the workbook?
PP Utility is still listing them in the list it creates in the workbook.
This sounds like a PP Utility issue, not a Power Query issue. You need to go back to the provider of PP Utility with your request as there is nothing I can do about how PP Utility has been set up, sorry.
Mynda
The VBA works on the current file status, no storing, no reference to previous lists, nada => no chance of forgetting to delete old records on the VBA side.
The VBA is "simply" reading record sets from ActiveWorkbook.Model.
sQuery = "SELECT DISTINCT [MEASUREGROUP_NAME] as
, [MEASURE_NAME] as [MEASURE] , " & _"TRIM( ':=' + [EXPRESSION] ) as [DAX Expression] , [DESCRIPTION] " & _
"from $SYSTEM.MDSCHEMA_MEASURES WHERE MEASURE_NAME <> '__XL_Count of Models' " & _
"AND EXPRESSION > '' ORDER BY [MEASUREGROUP_NAME] + [MEASURE_NAME] ASC"
So it is certainly something with the storing of the Measure related info in one of the many power pivot (xml?) files themselves.
Most likely, Matthias, but I'd say it's by design because it's not affecting how Excel runs. It's only apparent to you because you're using the PP Utility. As this is an add-in that isn't provided by Microsoft you have to go back to the developer of PP Utility and ask them to somehow ignore the unused implicit measures. I can't help you, sorry.
Thanks Mynda, the VBA does exactly what it is suposed to do - it reads the stored information about calculated measures and automatic measures the same way. The only difference is that the calculated measures can be easily deleted, thus the question if there is any way to eliminate also the automatic measures. If you don't know a secret path to where they can be deleted, then that's it - can't be helped. Thanks anyway!
That's what I'm saying...I don't know a secret path. I presume it's by design that you can't easily delete them. I imagine you'd have to unzip the .xlsx file and find where the list of measures is stored. Warning: do this at your own risk! 🙂
Mynda Treacy, there's not secret path nor is it a matter of design.
How to delete implicit measures in Excel 2010 with PowerPivot.
(This is translated from Spanish version.)
[1] Click on tab File in PowerPivot.
[2] Click on Show Advanced Mode.
[3] Then it appears Advanced tab and click it.
[4] Activate Show implicit Measures.
The inactives implicit measures are gray shaded, the actives one are in intense black.
Always appear explicit measures in intense black.
Right click on the name and select Delete. Follow the instructions in the dialog box.
I hope it is useful.
Arthur
