Preventing and disabling implicit measures

Preventing and disabling implicit measures

Power BI tends to automatically create so-called implicit measures when importing data. This happens when Power BI detects a field that can be aggregated and then generates an automatic aggregation, often a sum.

In most cases, this behavior is undesirable. Here’s why:

Disadvantages of Implicit Measures

  1. No Control Over the Base Measure
    When creating an explicit measure (e.g., summing revenue), you have full control over the logic and can reuse this base measure in other calculations, such as revenue for the same period last year.
    With implicit measures, you cannot edit the base measure because Power BI manages it automatically.
  2. Unexpected Results for End Users
    Implicit measures don’t always make sense. I once worked with a dataset containing car mileage data. Power BI summed the mileage, which was misleading for end users. Instead, we needed to analyze average mileage, not total mileage.
  3. Poor Manageability
    Implicit measures are difficult to control. Users might unknowingly perform analyses that your BI team does not intend. This can lead to inaccurate reports and misinterpretations.

Performance Considerations
It's worth noting that the performance of implicit and explicit measures is the same. The difference lies in flexibility and maintainability, making explicit measures the better choice in most cases.

For a more detailed discussion on implicit vs. explicit measures, I recommend this article from Radacad:

https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi

Preventing implicit measures

You can prevent Power BI from creating implicit measures by adjusting the report settings before importing data:

  1. Open Report Settings for the current file.
  2. Uncheck "Default summarizations" before importing any data.

Disabling implicit measures in an existing report

Now, when you are working in an existing report and want/need to disable implicit measures. The slow way would be to click on each column and disable auto summarization. I had to do this in a quite large dataset recently, which became so mundane I went looking for alternatives. This lead to me a Power BI forum post talking about the model view. In here you can select multiple columns at once and set the auto summarization off for all of them. Here's a step by step guide on how to do this:

  1. Open the model view
  2. Select column from a table with implicit measures
  3. Press CTRL + A to select all columns
  4. Go to the advanced options in the right pane
    1. Select "None" in the Summarize menu

This allows you to disable auto summarization for multiple columns at once.

Summary:

Implicit measures may seem convenient, but they reduce control, create unexpected results, and make reports harder to manage. Best practice is to disable them and always use explicit measures for clarity and flexibility.

Further resources:

  • An interesting thread talking about how Excel is bad at working with implicit measures.
Analyze in Excel - Implicit measures vs. Explicit measures
by u/humoon88 in PowerBI
  • The official Microsoft Documentation on calculation groups, explaining that you can't use calculation groups and implicit measures at the same time.
Create calculation groups in Power BI - Power BI
Learn how to create calculation groups in Power BI.