I love the Power BI community. There are so many great things that the community does. They help each other (think the Power BI community forum) and they also build fabulous third party external tools to enhance Power BI.
Measure Killer
This week I am sharing a brand new third party Power BI external tool called Measure Killer created by Gregor Brunner. At this writing, the tool is still in beta, so make sure you backup your Power BI workbooks before using this tool. In fact, that is good advice for all Power BI authoring, regardless of what you are doing.
Versions of Measure Killer
There are 3 options currently available.
- There is an MSI that requires Admin rights.
- There is a portable version that does not require admin rights and can be installed on a USB.
- There is a version on the Microsoft Store that requires admin rights.
I installed mine from the Microsoft Store so that it automatically stays up to date with the latest version.
It’s Built for Import Mode
It is worth pointing out that Measure Killer works by connecting to the meta data in a PBIX file. PBI workbooks are XML files containing all the information needed to render a report. More correctly, an Import Mode workbook contains all the data. If you are using a thin workbook where the model is elsewhere (either live connect or another PBIX), then Measure Killer will not be able to validate either the measures, or the measure usage in visuals. Measure Killer is used for Import mode PBIX files.
Launch from the External Tool bar
Assuming you have installed one of the versions that requires Admin Rights, you can launch from within Power BI from the external tool bar.
Select the file and run
Because I am launching from within PBID, I clicked “select file” (1) then Run (2). You could also choose a different file from this dialog if needed.
List of Results
After running Measure Killer, I was given a list of objects (Measures, Columns) that were targets to be deleted because they were unused in the model.
Note the 2 red buttons above. One provides a C# Script to kill the measures and the other provides the M Code to remove the columns. I also opted to save the results in an Excel file. The Excel file provides rich information about every measure and column in the model including if it is used, and if so, where is it used.
Kill the Measures
In my testing, I generated the C# Script and then pasted it into Tabular Editor 3.
After deleting the measures, I ran Measure Killer again, and it gave me a much smaller list of measures to delete. My guess is these were dependent measures and could be deleted once the up stream measures were deleted in the previous run. I ran Measure Killer 3 times to get rid of all unused measures.
M Code to Kill Columns
The approach to removing columns is a bit different. From what I can see, Measure Killer copies the current Power Query code for each table, adds a new step to remove the unused columns and provides the entire advanced code to you. You can then cut and paste that code back into Power Query to replace the existing code.
Note the before (1) and after (2) code below.
What do you think?
The tool is still in Beta, so there is more work to be done. Having said that, I love it. Others have done something similar to this in the past (notably Imke Feldmann) however all previous attempts I have seen require a lot more manual intervention to get everything set up. Measure Killer makes a significant step forward in automating that process. I hope at some stage that it will be able to execute the C# and M code itself, further streamlining the process.
So what do you think?
Hello community! Is there a way to consume this metadata that the tool offers us in a power bi dashboard?
Is the tool also able to recognize relationships between measures? Example: Measure 1 calculates a metric that I need for Measure 2. In the report I only show Measure 2. Would Measure 1 then be marked as unused or as used?
Hi Matt,
Thanks for the informative post. Is there a way to delete unused TABLES from the data model. I recently joined an org and found out that there are a lot of unused tables in the various projects which are slowing down the performance. I would like to get rid of unused tables.
Any insights would be greatly appreciated.
Sorry, I’m not aware of any tool that can do this. I guess you may be able to do it in Tabular Editor, but you would have to check.
Thanks for your suggestion, Matt. I will try that.
Hi there,
What about the safety in combination with customer data? How save is it to use this tool? Is the data stored somewhere?
You will need to contact the vendor
I’m not a fan of the idea of deleting every unused column and measure. Other people may connect to the Power BI Dataset and write their own reports. Unless storage sizes get out of hand, we keep any column that is populated and correct. In practice, we have no idea how many reports may be connecting to our SSAS models. You just can’t safely delete things!
That said, I LOVE the Excel report. If I wanted a list of which columns and measures are being used in a report, Power BI Helper is the only tool I am aware of to provide that, and it takes forever to run against our large models. Measure Killer generates this report quickly. I may never use this tool to kill measures or columns, but I expect to use it often to document reports!
It’s not a requirement to do that, of course. This is a tool if you want to delete stuff and want to check first if it is being used. It cannot predict the business value of a column remaining.
This tool is brilliant. We have on on-premises Report Server, and have authors out there publishing some extremely bloated dashboards and reports.
Just for kicks I ran a 60MB report through this tool, and implementing its suggested haircuts to the main fact and biggest dimension via the M-Code option resulted in slashing it to 19MB.
Great work, Gregor! And thanks Matt for publicising it.
Thanks for sharing, Richard.
I have need this sort of capability for an age. A fab tool. Hope it has the capability soon to run the clean up itself
Me too. I can’t see why not. Tabular Editor can write back to the model including deleting measures, so there is no technical reason. I guess the developer needs to walk before running.
Hi, This is nice blog and more informative…. This is one of my lovable blog.
Hi Matt,
thank you for pointing this out to us. It’s great to have a clean Model. It is super useful when the model and the report are in the same file and I want to try out this tool.
On a larger scale there might be a corporate model and the report uses a live connection. I assume this wouldn’t work in this scenario? Also, if an Excel file is connected to the dataset and the measures are used in Excel, but not in the report one might unintentionally delete measures or columns.
I am still looking for a solution that elevates the log analytics function to analyse what measures and columns are actually used in reports, live connections, queries etc. This might give a whole picture of a dataset usage and what columns and measures to deprecate.
Best regards,
Konstantin
The tool connects to the metadata in the PBIX file on the desktop. A live connection doesn’t have this metadata in the workbook, so my assumption also is that will not work. Also if you are using Analyze in Excel, it wont work because the tool has no knowledge of that connection, correct.
This is correct. The tool works good, but only if you report is in the same file as your model. A live-connection will not work, either because your datamodel – technically speaking – is not using the measures or columns, or because the report (that is connected live to your model) cannot be analyzed.
Iam very pleased to read your article
Thanks & Regards
V.saibabu