top of page

Measure Killer

an external tool for Microsoft Power BI Desktop.

Cleaning up your Power BI tenant

Measure Killer can help you clean and optimize your tenant. This can drastically reduce dataset refresh times and thus lower the memory and CPU usage of your Premium capacity. Since we use the XMLA connection, the dataset can remain in the service. Additionally, we only analyze the metadata, we do not read the actual data in your dataset.

Deleting unused measures

Measure Killer can delete unused measures on its own if the user chooses so. Alternatively it can generate a C# script for the measures to be deleted via Tabular Editor manually.

Deleting unused columns

Measure Killer will generate an M-Code to delete unused columns. This code can be pasted into Power Query's Advanced Editor to remove these columns from the model.

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 deletion of calculated columns

  • Connection to SQL Server Analysis Services or Azure Analysis Services not possible

  • Columns named 'title' will appear as used (false positive), if any column or measure of the respective table is used in the report.

  • 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.)

  • LocalDateTables will be tagged as used automatically.

  • Offline mode (without an internet connection you can only use the free version).

  • McAfee antivirus might block Measure Killer. You need to manually whitelist it, see here.

Measure Killer Compatibility

legend

fully compatible

limited compatibility

currently not working

General:

  • .pbix (Desktop and Desktop RS)

  • .pbix (Uploaded to PBI Service)

  • Thin file uploaded to PBI Service)

  • DirectQuery

  • Composite models

  • Paginated reports

  • Analyze in Excel

  • Any type of live connection (SSAS, AAS, Datamarts)

  • .bim files / models stored in a folder etc.

  • Metrics (Goals)

Feature compatibility:

  • Row-level security

  • KPIs (created in the tabular model)

  • Object-level security (When an artifact is only used in OLS, Measure Killer will not detect it)

Compatibility of visuals:

  • Standard visuals (all, unless listed below)

  • Icon Map

  • Zebra BI visuals

  • Other custom visuals (We have not tested Measure Killer for all custom visuals)

  • Paginated report visual

  • Metrics (Goals) visual

visuals.png

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.

Since Measure Killer is rather new, 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.

The copying and pasting of visuals between live/thin reports may result in false positives (artifacts being wrongly identified as used even though they are not).

How to use "Measure Killer"

Blog post or YouTube video

Measure Killer - Download and Changelog

Current version 0.9.2

Also available in the Microsoft Store now (Admin version)

Release Notes for 01/05/2023
Version 0.9.2

[MISC]
- Fixed a bug with "filter on this page" and "filter on all pages"

Older versions

​Release Notes for 12/07/2022
Version 0.9.1

[MISC]
- Updated the installer

- Fixed offline bug where Measure Killer was not loading

​Release Notes for 12/05/2022
Version 0.9
 

[ FEATURES ]

- Added connection to shared datasets and live/thin reports (offline and via XMLA endpoint for premium workspaces)
- Improved results table

[UI]
- rearranged buttons and added some functional buttons e.g "back button"

- changed formatting of results table

Release Notes for 10/17/2022
Version 0.8

 

[ FEATURES ]

- Added option to kill measures directly in Measure Killer without using Tabular Editor
- Added feature to sort columns 
- Added detection of columns used only by incremental refresh
- Added (DAX) expression column for Excel output
- Added new report option (row by row) when saving the results file
- Self-update after killing measures (the user doesn’t have to click on run again)


[MISC]

- Added error handling for blank file selection
- var. smaller bug fixes

Release Notes for 10/06/2022
Version 0.7


[UI]
-You can now see how costly (in terms of storage space) your unused columns are
-Table overview of unused artifacts
[MISC]
- var. bugfixes and performance improvements

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 '.

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.

Documentation

Installation

There are two different versions. An "admin" version and a "non-admin" or portable 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.

For the basic and enterprise version the MSOLAP package needs to be installed to ensure XMLA connection. You might need to download and install it from Microsoft.

Used 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.

Running Measure Killer

  • There are three choices when starting Measure Killer:

  • Start

    • Single report and dataset: This is the basic way to use Measure Killer. Here, only one report/dataset at a time can be analyzed.

    • Shared dataset on local machine: This requires the dataset to be downloaded.

    • Shared dataset via XMLA: This option does not require the dataset to be downloaded. But PPU or PPC as well as XMLA read rights are required in the Power BI tenant.

Single report and dataset

  • Analysis of a single report

This will take you directly to the main page of Measure Killer. If you launch MK from within Power BI External Tools, the port will already be pre-selected (drop down field on the top left). If you start MK separately, you will need to select it yourself, however.

Now, click on Execute. Here you need to select the report and correct path, you will automatically see the last opened Power BI file on top of the list. Click Run to start the analysis.

Measure Killer is now analyzing the Power BI file. To show the user the progress and what is currently being done, a new window will appear. Once this process has finished, the results of the analysis are displayed in the main table.

You will be able to see which artifacts are used, the size of the columns and other information like on which page of the report a measure is used or the type of visual it is used for.

These results can be displayed in more detail and better organized using Document results. You are free to choose the type of file you would like to have MK generate (.csv/.xlsx/.txt).

The functionality of each button is explained in more detail below in "Various buttons".

Shared dataset on local machine

  • Analysis of multiple live/thin reports connected to one dataset.

First you need to make sure to have the dataset to be analyzed and all live/thin reports locally on your computer. We recommend putting all report files in one folder separately. You need to the dataset to be analyzed open in Power BI. Only then you will find its port in the dropdown. Click on refresh if you have just opened the file so MK can search for it again.

In the next step you need to add the dataset via "Add new file" first. Then you can select the live/thin reports. Either individually with "Add new file" or a whole folder full of .pbix files with "Live/thin reports from a folder". When all files have been selected, click Submit.

MK will double check which one of the files is your dataset and prompt you to confirm.

You will then be taken to the main window. Click Execute to start the analysis.

Now, Measure Killer will do its magic.

Once done, the results of the analysis will be displayed in the main table. You will be able to see which artifacts are used, in which live/thin report, the size of the columns and other information like on which page of a report a measure is used or the type of visual it is used for.

These results can be displayed in more detail and better organized using Document results. You are free to choose the type of file you would like to have MK generate (.csv/.xlsx/.txt).

The functionality of each button is explained in more detail below in "Various buttons".

Shared dataset via XMLA

  • Analysis of multiple live/thin reports connected to one dataset (via XMLA endpoint). This option is useful if the dataset cannot be downloaded. 

Requirements:

  • Basic or Enterprise version of Measure Killer

  • Premium Workspace

  • XMLA Endpoint at least with read rights

  • XMLA Endpoint allowed in the Power BI Admin Portal

  • MSOLAP package installed

Enter the name of the workspace where the dataset to be analyzed resides. This is case sensitive. Now hit either <<enter>> or click on Connect. Measure Killer will trigger you to log in to your Microsoft Account to check if it can find a workspace in your native Power BI tenant. It will pre-select a dataset for you, if that is not the correct one, click on the dropdown to change it. Click on Next.

Now you need to add the live/thin reports, these need to be on your local machine. Either individually with "Add new file" or a whole folder full of .pbix files with "Live/thin reports from a folder". When all files have been selected, click Submit.

You will then be taken to the main window. Click Execute to start the analysis.

Now, Measure Killer will do its magic.

Once done, the results of the analysis will be displayed in the main table. You will be able to see which artifacts are used, in which live/thin report, the size of the columns and other information like on which page of a report a measure is used or the type of visual it is used for.

These results can be displayed in more detail and better organized using Document results. You are free to choose the type of file you would like to have MK generate (.csv/.xlsx/.txt).

The functionality of each button is explained in more detail below in "Various buttons".

Various buttons in the user interface explained

  • Back (icon - top left): Takes you to the previous page to select the mode of Measure Killer.

  • Refresh (icon - top left): Gets the latest ports of all opened .pbix files.

  • Copy: Copies the text from the text box on the left.

  • No Idea what to do?: Sends you to our YouTube channel.

  • Execute: Starts the analysis.

  • Feedback: Redirects you to a blog post on our website where we collect feedback for MK.

  • Plot unused vs. used: Plots the results graphically (not a nice chart, we know, sorry!)

  • Document Results: Generates a detailed documentation as .xlsx, .txt or .csv file.

  • Kill unused measures: Allows you to either delete selected or all unused measures (see Features below for more information on this).

  • Kill unused columns: Opens a new window, where you can select all of the tables containing unused columns. These tables are sorted by the sum of storage space you can save in your model. It will generate an M code you can copy into the Advanced Editor of the respective table/query.

  • Reset Table: Resets the table to the default view.

  • Filter with drop-down menu: Here you can filter by the Name/Table/Report columns in conjunction with the text field.

  • GO: activates the filter from the text field on the left.

  • Measure Killer's official website: Redirects to the Measure Killer website.

  • Delete table content: Deletes the content of the table.

 

Features

  • Deleting 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. This step can be performed directly from Measure Killer. There is also the option to run the generated script using the tabular editor. Furthermore, the user has the option to delete either all measures or only selected ones.

  • Deleting 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.

  • Chart view: 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. We are aware that this visual is pretty ugly compared to Power BI.

  • Since Measure Killer 0.9 it is possible to connect to datasets via XMLA and analyze live/thin reports. There are two ways to do this:

    • Online (via XMLA): With this option, the dataset does not have to be downloaded. But PPU/PPC and XMLA reading rights are required and the live/thin reports need to be on your local machine.

    • Offline (shared dataset on local machine): Report files and dataset need to be locally.

  • Single report and dataset: This is the most basic way to use Measure Killer. It will analyze one report/dataset only.

  • When deleting Measures directly from Measure Killer, a C# script will be executed. This is why a cmd window will pop up showing the Tabular Killer.

bottom of page