Removing unused measures and columns in a Power BI report has never been easier.
With our release of Measure Killer version 0.9, it becomes effortless.
First, we need to open the report we want to optimize and then go to External Tools (make sure you have it turned on) and open Measure Killer.
You will then see two options, since in this blog post, we want to analyze a single report/dataset, we choose the first option (Single report and dataset).
There is another blog post about shared datasets and live report, check it out here.
In any case, now the main window will appear.
Measure Killer will connect directly to your local host (see button on top left - the port is selected automatically)
There are some new buttons in this version:
1. Get to know Measure Killer better (“No idea what to do? Click here!” button).
This will take you to the official YouTube video on how Measure Killer is used.
2. To provide feedback about Measure Killer there is a “Feedback” button.
3. To find the latest updates/changelog and all essential information about the tool, there is a “Measure killer’s official website” button.
Now let’s execute the Measure Killer for our sample report. After clicking on execute, you will need to select the report you want to run the Measure Killer on (of course it is the report we already have open - we need to select the path however)
The latest opened report will be preselected. If this is the correct one click run. Just to ensure that you are selecting the correct report we have added an additional pop up window which is double-checking we have not made any mistake.
When we click “Yes” Measure Killer will do its magic.
In this version we have also included this progress window below where one can see what Measure Killer is up to.
Once MK has finished, you will see "DONE" and a table of artifacts (columns and measures) will appear in the main window (see picture below).
The new view is providing additional information about the used artifacts (see below).
In this case we show the measure "Margin" which is used multiple times and thus it looks like in the screenshot below.
You will be able to see everywhere this measure has been used, in our case in a filter on page "Contoso Overview" in the measure [Margin %] and so on.
It is possible to “Kill unused measures” by clicking on this button. You will have three options to choose from.
You can either kill all the measures, kill selected measures or create a C# script and use the tabular editor to remove measures.
We hope this gives users enough flexibility in removing unwanted measures from their model.
Columns can be removed in another way, by clicking on “Kill unused columns”. A new window will pop up where you can select a table. Only tables with unused columns are shown here, and they are sorted by
You can delete unused columns by choosing a table from the dropdown on top.
Now this gets interesting, since Measure Killer will pre-calculate the storage you can save by removing all unused columns in a respective table.
So the tables in the dropdown are sorted by the total amount of storage space that can be saved, if we remove all unused columns.
Meaning that the 102542 bytes for factWeather or ~103KB (uncompressed) are the sum of all unused columns in this table.
To prove this, you can see the screenshot below, where we can also verify MK's numbers with Dax Studio's VertiPaq Analyzer (there can be tiny deviations).
Now that I have selected factWeather, we can see the full M code of this query (see below).
What Measure Killer does is copy the original query, then add a step at the end to remove the unused columns (orange highlighted code).
Now we can click on “Copy text” and paste this 1:1 into our Advanced Editor in Power Query. This is the safest way to remove columns, since we can always later remove this step if we change our mind and want to keep them in.
We realize that there is manual work needed here, as of now, we have not come up with a more automated solution however.
One more new feature in 0.9 is the ability to search for an artifact (measure or column, in the UI this is called "Name" since we search for the name of the artifact) table or report (report does only make sense if we have shared datasets and multiple live reports which we do not want to get into further in this blog post)
We built this feature since larger organizations or generally big datasets might have thousands of artifacts and thus a search makes more sense.
The search is neither case sensitive nor does it only search the beginning of the text.
I can search e.g. for all measures and columns with a "%" in their names.
Don't forget to press on "Go" or hit enter on your keyboard.
If you like what we are doing or want to give us feedback, please do so here.