Advanced Optimization for Power BI Models with Measure Killer's new "Clean Your Model" feature
- Klaus Folz
- vor 1 Tag
- 9 Min. Lesezeit
Introduction
Deleting unused measures and columns is good practice. It usually reduces model size and can improve refresh and DAX performance. But even then, many artifacts might technically be used but are still unnecessary.
For example used in hidden visuals, hidden pages, or field parameters that nobody actually uses. That is why Measure Killer includes the "Clean your model" tab (shown in the image below), it helps you remove not only unused artifacts, but also unnecessary ones that are still referenced somewhere in your model or reports.
Note: Details in this tab are only shown for paid licenses, request a trial here.
What Measure Killer does
After you analyze a model, Measure Killer finds artifacts that you can safely remove.
An artifact is flagged as unused if it is not referenced in your model or the reports connected to it. If an artifact is flagged as unused or "used by unused", you can delete it without breaking anything. This is the core goal of Measure Killer. The image below shows the results tab of the tool, listing all measures and columns from the analyzed model and if they are used or not.
Unused vs. unnecessary
These are not the same thing:
Example 1: Unused
A column that is never used in any visual or DAX expression.
Example 2: Used but unnecessary
A column used in a hidden visual that is never activated by a bookmark. An example is shown in the image below.
Measure Killer will classify example 1 as unused and example 2 as used. But in practice, both can be "killed" safely. Finding cases like example 2 is exactly why the "Clean your model" feature was created.
![An example of a column ('factCopy'[Stadt]) used only in a hidden visual that is not referenced in a bookmark.](https://static.wixstatic.com/media/fd76fb_d3c7980c43d74b35b4e23cc71a0a6294~mv2.jpg/v1/fill/w_147,h_82,al_c,q_80,usm_0.66_1.00_0.01,blur_2,enc_avif,quality_auto/fd76fb_d3c7980c43d74b35b4e23cc71a0a6294~mv2.jpg)
How the "Clean your model" tab works
After analysis, the Clean your model tab shows a list of suggestions (See the image below). Each suggestion represents one "cleanup" case, such as but not restricted to:
Artifacts used only in hidden visuals
Pages with no views
Auto-detected relationships
Unused field parameters
For most suggestions, Measure Killer also estimates how much model size you can save.
In the next section I go through every suggestion in detail and explain the logic behind each one. This part is especially useful if you want to understand why Measure Killer makes these suggestions and how each works in practice.
All "Clean your model" suggestions in Measure Killer
Before reading the list below, it is important to understand one key rule: Most suggestions only appear when the artifact is used in that one single situation, and nowhere else. For example: if a column appears only in a hidden visual that is never activated by a bookmark, it may show up in that suggestion. But if the same column is used anywhere else in the model or report, it will not appear there. Below, all possible suggestions are listed:
1 - Remove unused artifacts. 'Export clean TMDL' is the fastest option.
This is the most basic suggestion and the one you should start with.
Measure Killer finds all artifacts (tables, columns, measures, and relationships) that are not used anywhere at all or only used by another unused artifact, that is what the tool flag as "used by unused", usually highlighted in yellow.
You can remove them in four different ways:
Export clean TMDL is the best and fasted option and the "how to" is in the image below. Note that this option does not create a backup automatically, its on the user to do that (best is to add the current model to a new tab as TMDL script)
Use the “Kill” tabs to remove measures and columns.
Use 1-click cleanup for .pbip files.
Or delete them manually in Power BI.
How to use the "Export clean TMDL" option in Measure Killer to remove all unused and used by unused artifacts in a Power BI model.
2 - Remove unused DAX Variables from artifacts
Measure Killer scans all measures, calculated columns and tables looking for DAX variables that are defined but never referenced. When it finds them, it suggests removing these variables. The quickest way to check which variables are unused is to expand the suggestion in the table, right-click on the artifact and go to "DAX expression with violations", a window will pop up and highlight the unused variables and other DAX best practices violations (See the image below).
After removing the unused variables inside the DAX expressions, we recommend analyzing the model again, because removing variables can reveal new unused artifacts (currently the "used by unused" logic does not apply for DAX variables).
3 - Disable Auto Date/Time
The worst nightmare of every Power BI developer is having the auto date/time setting on. In my opinion, this is the worst feature ever created in Power BI, and it is on by default. If your model has it on, the tool will:
Give you a generic calendar table DAX expression for you to create a proper calendar table.
Track where auto date columns and hierarchies are used so you can replace them in your visuals (so they will not break when we turn off auto date/time)
Turn off auto date/time in File - Options - Data Load as shown below.
How to turn off Auto datetime in Power BI desktop.
4 - Remove field parameter tables that are not actually used
Field parameters are a special case, because their columns reference the other columns in the same table, creating a sort of self reference. This behaviour can be observed in the DMV DISCOVER_CALC_DEPENDENCY. If a field parameter table is only used by itself and nowhere else (An example is shown in the image below in the results tab), Measure Killer treats it as unnecessary.
In that case, it suggests deleting:
The field parameter table
Any columns or measures that are only used in the field parameters table
An unnecessary field parameter table where all its artifacts only reference each other, as shown in Measure Killer's Results tab
5 - Delete relationships connecting to a table that is not used by anything else, then delete the table.
These relationships connect one or more tables of the model to a table that is otherwise unused (see the image below). If the only reason the table is flagged as used, is that one relationship, Measure Killer suggests:
Delete the relationship
Then delete the table
6 - Remove hidden pages not used as tooltips, drillthrough or with page navigation.
A page will be flagged there, if all of the following are true:
It is hidden
It is not a tooltip page
It is not a drillthrough page
If this happens, Measure Killer suggests deleting the page and then removing any artifacts used only on that page as shown below.
7 - Remove hidden visuals not referenced in bookmarks. Then, remove artifacts only used in those visuals.
This suggestion works at the visual level.
If a visual is:
Hidden
Not referenced in any bookmark
Then Measure Killer recommends deleting that visual and any artifacts that are used only in that visual. The image below shows the "Stadt" column and how this suggestion is displayed in the Clean your model tab.
Important:
Personal bookmarks created in the Power BI service are not considered in this suggestion.
If the column or measure is used somewhere else, it will not appear here.
8 - Consider removing reports which have not been consumed in the last 28 days by unmarking them before running Measure Killer.
In the Shared Model Online modes (Admin or Developer), Measure Killer can check report consumption in the Power BI Service. If the report page views and opens were fetched successfully and the report had zero opens in the last 28 days it suggests:
Unselecting that report
Running Measure Killer again
Then deleting artifacts that are used only in that report
This suggestion only appears if "Get report page views and load times" setting is enabled.
9 - Remove pages with no views. Then remove artifacts only used on those pages.
Similar to suggestion 8, but at the page level instead of the report level. If a page had zero views in the last 28 days, Measure Killer suggests:
Deleting the page
Removing artifacts used only on that page
Again, this requires the "Get report page views and load times" setting. Additionally, you can check page views in the Report results tab as shown in the image below (for detailed page views right click on the page or report level).
10 - Verify if auto-detected relationships are necessary in the model. If not, delete them.
Power BI creates relationships automatically if the "Auto detect new relationships after data is loaded" option is enabled (Shown in the image below). If your model has auto-detect relationships, Measure Killer will suggest to verify if you really need this relationship. If not, it recommends deleting it. Probably the hardest suggestion to apply, verifying if you need an active relationship is usually not straight forward.
11 - Remove artifacts used only in filters without a filter applied and if not activated by any bookmark.
Some columns are used only in visual, page, or report-level filters. If the filters:
Have no active filter query (shown as "is (All)" in Power BI)
Are not activated by any bookmark
In this case, Measure Killer suggests:
Removing the filter first
Then deleting the artifacts that were used only in that filter
The image below shows an example of a column that would trigger this suggestion, the column is used only in a visual level filter, without a filter query and the visual is not referenced in any bookmark (Not in the image, but believe me it isn't).
12 - Verify if you need columns only used as dynamic M parameters and not in any other way.
If a column is used only as a dynamic Power Query (or M) parameter in a table in DirectQuery mode and not in slicers or anywhere else, Measure Killer suggests to delete the column. To unbind a column from a dynamic M parameter, open Model view in Power BI Desktop, select the column in the right pane, and clear (or change) the Bind to parameter dropdown as shown below.
13 - Verify if you need unused key columns in import mode tables (DirectQuery tables and Hybrid not considered).
For Import tables, if a column is marked as a table key but is not used anywhere else, Measure Killer suggests to:
Unmark it as a key
Check that relationships and DAX still work
Delete it if everything is fine
DirectQuery and Hybrid tables are excluded. To set or remove a key column, open Model view in Power BI Desktop, select the table in the right pane, and choose (or clear) a column in the Key column dropdown as shown below.
14 - Verify if you need columns only used as table's row labels.
If a column is set as the table’s Default Label but used nowhere else, Measure Killer suggests:
Unmarking it
Checking visuals and measures
Deleting it if nothing breaks
To set or remove (unmark it) a row label, open Model view in Power BI Desktop, select the table in the right pane, and choose (or clear) a column in the Row label dropdown as shown below.
15 - Consider ignoring the semantic model's report (pages) since it is a shared/golden model in the service.
Every shared (golden) dataset published from Power BI Desktop creates a report automatically. Often this report is empty or only used for testing. Measure Killer checks which artifacts are used only in that dataset report and suggests:
Excluding that report from analysis
Running Measure Killer again
Removing artifacts used only there
This suggestion will only appear for shared (golden) semantic model architectures, with 2 or more reports connected to the same model. Removing artifacts that are used only in the semantic model's default report may break visuals in that report. Measure Killer therefore leaves the final decision to the user. An example of this suggestion is shown in the image below.
16 - Disable isAvailableInMdx for used columns that are not used in Analyze in Excel.
If a column is used in your model, but not used in Analyze in Excel and still has isAvailableInMdx = true, Measure Killer suggests turning it off to reduce model storage size. To change this property, you can either edit it directly in TMDL view or use Tabular Editor, as shown in the image below.
Conclusion
The Clean your model tab in Measure Killer helps you take model optimization one step further. It does more than just remove unused artifacts, it also helps you spot unnecessary ones in tricky situations like hidden visuals, unused pages, auto-detected relationships, or shared dataset reports. This feature is especially useful for intermediate to advanced Power BI developers who want cleaner and smaller models.
You can download Measure Killer here.
To request a free trial and test the Clean Your Model feature click here.