Log In
Register
Home
December 13, 2017
Offline
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.
See Answer
July 16, 2010
Offline
Hi Matthias,
What version of Excel do you have? I don't get this behaviour in Office 365.
Mynda
December 13, 2017
Offline
Hi Mynda,
I am non 2013. And the measures are listed by PP Utilities (List measures): https://www.sqlbi.com/tools/po.....utilities/
Thanks,
Matthias
July 16, 2010
Offline
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?
December 13, 2017
Offline
PP Utility is still listing them in the list it creates in the workbook.
July 16, 2010
Offline
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
December 13, 2017
Offline
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 [TABLE], [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.
July 16, 2010
Offline
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.
December 13, 2017
Offline
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!
July 16, 2010
Offline
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! 🙂
New Member
July 2, 2019
Offline
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
Answers Post
1 Guest(s)
