Removing unused measures and columns in a Power BI report has never been easier.
Please download Measure Killer from the Microsoft store or the official website.
Start the tool and select mode 1 (mode 1 and 2 are part of the free version of the tool.
There is another blog post about how to optimize a shared semantic model (mode 2)
Now we will see a window where the report we have currently open in Power BI Desktop (important!!!!) is preselected.
If you have multiple Power BI files open make sure to select the correct one.
Let's hit next - the main window will appear.
We need to select run and double check that the "Sales Report" is selected in the window
Now we can see the results
At the bottom left we have a nice little summary of our model. We can see how much of the size (MB/GB whatever) is actually used in the report.
This is calculated by the size of all columns used / all columns.
In my example only 54% of the size of my model is used - meaning my model is almost double the size it needs to be.
In the main window we can see all columns, calculated columns and measures listed. It also shows us where they are used or if they are not used at all.
Used means a column is referenced in a visual, conditional formatting, filter or via a measure etc. It even checks Power Query (used in a join and so on).
The whole promise of the tool is that if it tells me something is not used I can delete it without any impact on my report or model.
Now let us remove all unused columns, measures and tables.
If we have a .pbip file we can actually do this in one click where Measure Killer creates a new file for us with everything "unused" removed. (pbir not required)
If we have a .pbix we can do it semi-automatically.
First let us kill those unused measures and calculated columns in one click
After hitting "Kill" a new window should appear showing if everything went correctly.
If anything went wrong here or you want to revert there is a "Restore" feature in the main window to ressurrect your dead measures.
Next we can remove the columns from Power Query (here we need to get our hands dirty).
For every query in our model the tool will generate a new M code where it adds one step in which it takes out all the unused columns. We can decide if we want to do a "Remove columns" or "Remove other columns".
We just copy the code and paste it into the Advanced Editor in Power BI. Make sure to paste it into the correct query (in my case the f_Sales_main query is shown so I need to change that M code)
You can see that after pasting the code into my query there is one new line of code at the bottom which is also shown in the user interface after I click "done".
After we did this for every query that has unused columns (only those will be listed in Measure Killer) we are done and can hit "Close and apply" in Power Query.
If we now "Run" Measure Killer again we will see that the summary at the left bottom has updated to 100% used.
Beware of the size shown in the brackets (in my case 3.6 MB - it is wrong at this point) this will only be shown correctly if you restart Power BI after you changed your queries.
You can provide feedback about the tool here.
Comments