Measure Killer - open beta

Measure Killer is an external tool for Power BI Desktop.
The main feature is deleting unused measures and columns.

Deleting unused measures

Multiple measures can be deleted at once using a C# script. This script can be executed in Tabular Editor's "Advanced Scripting" tab.

Deleting unused columns

Measure Killer will generate an M-Code to delete unused columns.

Analyzing reports

For every report analyzed, Measure Killer will generate a comprehensive Excel file to show where an artifact is used. This can be in: Measures, Visuals, Filters, Conditional Formatting, Joins in Power Query, or other parts of a Power BI Report such as Relationships.

Furthermore, Measure Killer can generate a chart to show the number of unused and used columns and measures.

Analyse_Icon_edited_edited.png
Columns_Icon_edited_edited.png
Measures_Icon_edited_edited.png

What does Measure Killer detect?

  • Visuals - including filters applied to visuals, on pages or the whole report.

  • Any kind of measure or relationship

  • Columns used exclusively in Power Query, e.g. in joins

  • Calculated columns

  • Calculated tables

  • Conditional formatting

What does not work?

  • No connection to Live (Thin) Reports

  • No deletion of calculated columns

  • Due to "2nd class measures" several runs may be necessary to delete all measures. (These are measures that are only referenced in other measures but are not used in a visual, for example.)

  • If a measure or a column's name contains one of these characters: ,  " or ' there might be errors or false positives when running measure killer. 

  • LocalDateTables will not be considered and therefore will remain in the model.​

Important information and versioning

There is one version which requires admin rights, here, Measure Killer will automatically be added to the External Tools in Power BI. The portable version, which does not require admin rights, will not show up as an external tool.

Measure Killer is still in beta, there may be bugs, if you experience any, please let us know here.

Measure Killer creates a hidden "temp_" folder for each report analyzed. This folder will be saved in the same folder as the Power BI file. The temp folder is nothing else but an extracted .pbix file.

How to use "Measure Killer"

Measure Killer - Download and Changelog

Current version (beta)

Release Notes for 9/16/2022
Version 0.5


[UI]
– Rearranged buttons.
[MISC]
– Fixed an error that caused false positives if measure names would contain " and '.

Previous versions

Release Notes for 9/7/2022

Version 0.4


[ FEATURES ]
– Added M code to automatically remove columns.
[UI]
– Improved UI and added colored buttons.

Release Notes for 8/27/2022
Version 0.3


[ FEATURES ]
– Added C# script to remove measures.
– Added option to plot results.
[ MISC ]
– Removed info page on startup.

Also available in the Microsoft Store now (Admin version)

​Documentation

Installation

There are two different versions. An "admin" version and a "non-admin" version. In order to be able to install the admin version, the user must have administrator rights in the system. But basically the two versions work exactly the same. The only disadvantage of the "Non-Admin" version is that Measure Killer will not be automatically added to the "External Tools" in Power BI Desktop.

Verwendete Python libraries

sys, pyadomd, pandas, numpy, tkinter, os, json, shutil, re, subprocess, psutil, pyperclip, webbrowser, matplotlib
 

Instructions

Measure Killer can be easily opened in the "External Tools" in Power BI. If the "Non-Admin" version is used, the tool must either be run via the .exe file or added manually to the "External Tools"​​​. The port will be automatically added from the report in which the Measure Killer was started. In addition, the last opened .pbix  file is pre-selected as the file path.

Various Buttons

  • Run: Analyzes the report and returns the unused columns and measures as a result.

  • Refresh: Refreshes Measure Killer and retrieves the latest data from the report. This feature can be very helpful when working with "2nd class measures" in order to save time even with multiple runs. 

  • Show Info: Opens a window with Measure Killer's features and limitations.

  • Save Results: Saves the results in an Excel file.

  • Plot Results: Displays the results graphically.

  • Show All: Shows all measures and columns of the report instead of only the unused ones.

  • Show unused: Shows all unused measures and columns in the text window.

  • C# script to kill measures: Generates the C# script in the text box. The script can then simply be copied with the "Copy text" button and inserted and executed in the tabular editor in the "Advanced Scripting" tab. It is important to save the changes in Tabular Editor after executing the script. 

  • M code to kill columns: Opens a separate window in which the desired table can be selected at the top left. Here the M code is generated for each table. This code must then be copied into the Advanced Editor of the respective query.

 

Features

  • Delete unused measures: Measure Killer scans the report and returns all measures that are not used in any way in the report. Multiple measures can be deleted at once using a C# script. To do this, the script generated by Measure Killer must be inserted and executed in the Tabular Editor in the "Advanced Scripting" tab.

  • Delete unused columns: Measure Killer scans the report and returns all columns that are not used in any way in the report, so you can safely delete them. However, the tool should still be used with some caution. Multiple columns per table can be deleted using an M code (Power Query Script). However, this script must be run for each table individually. Unfortunately, it is not possible to run it on multiple tables simultaneously.

  • Analysis of the report in Excel: A detailed Excel file is created for each report, here you can see where an artifact is used. This can be in: Calculations, Visuals, Filters, Conditional Formatting, Joins in Power Query, or other parts of a Power BI report such as Relationships. In addition, Measure Killer also outputs a chart if desired, showing the number of unused columns and Measures.

  • Analysis of the report in a chart: There is also a graphical analysis in form of a chart. The used and unused measures and columns are displayed in a bar chart. This feature can be accessed via the "Plot Results" button.
     

Limitations​

  • No connection to Live (Thin) Reports​

  • No deletion of calculated columns

  • Due to "2nd class measures" several runs may be necessary to delete all measures. (These are measures that are only referenced in other measures but are not used in a visual, for example.)

  • If a measure or a column's name contains one of those signs: , or " or ' there might be errors or false positives when running measure killer. 

  • LocalDateTables will not be considered and therefore will remain in the model.