Power BI Calculation Groups and Implicit Measures

As a Business Intelligence (BI) consultant, it is essential to understand the specific needs of clients before designing a custom solution. Our team at Smoother Consulting has partnered with a Managed Service Provider (MSP), designed and implemented multiple Power BI solutions tailored to their unique business needs. A recent project with our client included the use of calculation groups, report filtering based on measures, and moving DAX columns to Power Query to improve performance on large data sets.

Usually, when adding a numerical field to a section of a visual, the user can click on the down arrow and select an aggregation type (e.g., sum, avg, min, count, etc.) - or what we call implicit measures. The aggregation part of the drop-down menu, circled in red, was no longer available in our PBI file.

As data modeling nerds, this was a very fun project, but not one without its unique challenges.

During the testing phase, our client asked why they could not choose the aggregation type after adding numeric fields to their visuals. This led us to discover a behavior in Power BI that was triggered by the use of calculation groups.

What was the problem?

When a Calculation Group is enabled in a Power BI model, it is not compatible with implicit measures. Implicit measures are measures that are automatically created by Power BI based on the fields that are used in a visualization. There is not a visible DAX formula that can be seen by the end user without using external tools, but Power BI creates these measures in the background when the user selects an aggregation type. In contrast, explicit measures are measures that are created by the user using DAX formulas.

We like this article for an explanation of explicit vs. implicit measures: https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi

When a Calculation Group is created, Power BI makes certain behind-the-scenes changes to the model that disable implicit measures. This means any aggregations that rely on implicit measures will no longer work.

Okay…so how do we fix this?

To resolve this issue, there are a few different options. One option is to delete the Calculation Group and enable implicit measures in Tabular Editor. This will allow the client to use aggregations again, but it will also remove any calculations that were created within the Calculation Group.

In Tabular Editor, if you click on Model, information about the connected model will show up. Under Options, there will be an object called “Discourage Implict Measures”. By default, this is set to False. When a Calculation Group is added to the model, Discourage Implicit Measures is set to True and there will be no option to set it back to False unless the Calculation Group is removed from the model. Only when this setting is set to False will the user be able to use implicit measures.

Another option is to build explicit measures to replace the use of implicit measures and aggregations. This can be done by creating DAX formulas that replicate the behavior of the implicit measures. For example, if you want the distinct count of Employee ID, you may consider creating a measure like:

DistinctCount_Employee =

DISTINCTCOUNT ( 'Table'[EmployeeId )

Additionally, a new calculation group can also be created to replace all the aggregation types you want to use.

In our specific project, we worked with the client to build explicit measures to replace the missing aggregation functionality. Then we taught them how to use the new measures in their visualizations.

Conclusion

As of this writing, the inability to use implicit measures is a limitiation imposed by Calculation Groups. Perhaps, one day that will change. However, for multiple reasons not explained in this article, it is best practice to use explicit measures when trying to aggregate values in your dataset.

As a consultant, it is important to not only have the technical expertise to design a solution, but also the ability to teach clients how to use it. Understanding the client's needs and how they intend to use the product is key in creating a tailored solution that meets their specific requirements. Additionally, providing education on best practices and troubleshooting common issues can help ensure a successful implementation and continued use of the BI solution.