[PowerBi] Measure Killer - external tool to clean model

[ibarrau]

If there is something beautiful about the PowerBI community, it is how people help each other to achieve common goals. This is how these tools emerge. The tool is called Measure Killer and was created by Brunner_BI. You can find it at the following link (current version at May 10th 2024): https://www.measurekiller.com/downloads/2.1.0/Measure_Killer_Setup.exe

Or you can also look for it at Microsoft Store.

It can be installed as an MSI, through a store, or as a portable version. Naturally, I will use the MSI for integration into the external tools menu. On the website, you can also clarify any doubts about what it can and cannot do.

Some of the tool's features involve analyzing a model in terms of DAX measures, columns, and tables, as well as Power Query. This helps us clean up the model.

In this article, we will look at its most popular feature: finding unused measures and generating a C# script to remove them in one step. If you are not familiar with C#, you can refer to this old blog post.

As soon as we open it, we will have a pre-menu to select the dataset. Let's go for Single report/dataset."

Soon we'll see the main screen  and a short doc from the web in order to explain how it works in more details.

First, in this menu, we will look for the file we have open to ensure a correct mapping between the file and the local server, or at least that's what I imagine it does. Secondly, we click on "Run," and thirdly, on "Show results." We will end up with a screen similar to the following:

In white, we will see columns and measures that are used, and in red, the ones that are not used. Here we can explore, search by name, table, filter by usage, etc. When we are ready to take action, we go to the 'Kill measures and columns' menu, where we will see various options for bulk execution.

I recommend not using the "Kill all..." option to have a double check of our process. If we use the option for selected items, we can see a preview list that helps us have another overview. My favorite is the C# script because it not only gives me the same overview for a revalidation before deletion but also because I could save that script in model documentation folders to know the date, who, and what elements were cleaned. This way, we have a better understanding of the model process.

While the tool allows us to export information for documentation purposes, I do not believe that the proposed columns provide more than other tools we have seen, such as "Model Document." A simple example is that the measure description is not among the exported columns. The documentation mainly aims to answer where the measure is being used and what its expression is. However, I consider it important to execute the action along with the cleaning script. This way, if we need to recreate a complex measure that we suddenly need, we can track if it existed and when it was deleted in the script and then search for its expression in the exported Excel file with similar dates. You would definitely need a good naming rule for those files.

Thus, we finish looking at a simple tool that is gaining popularity for its simplicity and potential.

Original post in spanish from LaDataWeb

[Brunner_BI]

Thanks for posting @ibarrau 

[Tlotly]

Thank you @ibarrau  for this fantastic post. I just discovered it today and immediately applied it to one of my dashboards. The size decreased from 175,000 KB to 48,000 KB, significantly enhancing performance.

kudos to @Brunner_BI  for this amazing tool.