Combining Calculation Groups

Users often have more of them within their solution or Semantic Models. Because of this, each calculation group has its own Prescendense, which determines the order of execution if several of them are selected simultaneously within one calculation. You must always have one calculation group with the same Prescendense. This behavior is necessary for the calculations to work correctly, so it is necessary to set the evaluation in the correct order. Sometimes, however, it is possible to get to a point where it would be necessary to somehow combine calculations from different calculation groups, which, under normal circumstances, are in a different order.

In the article Conditional Formatting with Calculation Groups, it was explained that functions of the SELECTEDMEASURE() type can be used within calculation items, which dynamically allow any measure to be used, with which the item comes into contact and its calculation to be extended or wholly changed. This principle can also be used to combine calculations from different groups of calculations. At the same time, in the article Field parameters in cooperation with Calculation groups, it was shown that it is not always necessary to use these functions, but that the user may need/want to use the calculation group, for example, to switch between different existing measures, which can also be achieved using field parameters. However, if we want to combine various linked modifications of calculations from different calculation groups, it is pretty practical to stick only to calculation groups and not field parameters.

Measures and Calculation Groups How measure can be changed by Calculation Groups

The picture shows that the input measure [blank] is replaced with the measure [# Costs] using the first Calculation Group. The next Calculation Group then expands this measure, where thanks to the SELECTEDMEASURE() function, only the replaced measure from the first Calculation Group is transferred and causes it to display only the TOP 5 or BOTTOM 5 values, which can be used in visualizations. If the Range Parameter extends this calculation, users can also dynamically choose how many values they want to display. The numerical designation in the names of Calculation Groups is here for better orientation and to express their Prescendence.

Calculation Group applied in Chart Applied calculation groups on Bar chart

The advantage of this approach is that users can avoid creating new measures that would be unnecessary and could cause unnecessary complications within the Semantic Model. At the same time, it is a dynamic option where the mentioned TOP/BOTTOM calculation can be applied to essentially any input measure.

This type of calculation is very often used so that users have an easier orientation in the data and can focus more quickly on the most significant values. In such a case, however, it is beneficial to expand the visual with another option supporting the ability to absorb specific information. Thanks to colors, this is often done in Power BI. However, it depends a lot on the scenario currently being prepared for the end users. Sometimes, it may be necessary to show only the highest or lowest set values or those that exceed some limit or to build a gradient that supports visual perception.

In the article Multi-Color Gradient with DAX in Power BI, it was shown how DAX and its functions can be used to create the gradient just mentioned. What if we wanted to combine calculations from different groups and use the gradient simultaneously? For example, if the user selects TOP values, these values would be displayed with a green gradient, gradually lightening. If the BOTTOM value were selected, these values would be displayed with a red gradient. At that point, it would be easy to recognize which side we are looking at the data on and what the maximum or minimum is.

However, we run into the problem that such a calculation requires the ability to execute the given procedure, which comes from individual calculation groups. At the same time, however, we must comply with the conditions for conditional formatting of visual aspects such as Bars because otherwise it would not be possible to use them. An essential condition is that the inserted measure must return a color. It is more or less what and how it is defined. So, it can be a black color defined by the HEX color code. RGB, HSL, HSV, and so on can also be used. Another condition is that after all calculations or changes made using Calculation Groups, the output must still be a valid color. However, if we were to apply a general Calculation Group that is supposed to transform the input measure into the color that is to be used, at the same time, we wanted to use the previous Calculation Groups. We need to be aware that this new calculation group must always be at the end, but at the same time, it will also affect the calculation itself, not just the one that should explicitly return a color. So, both the measures used to return the values and the ones to return the color would return the same result. Suppose you thought it could be parameterized using the ISSELECTEDMEASURE() function. In that case, unfortunately, you will run into the problem that as the first Calculation Group overwrites the input measure, all subsequent SELECTEDMEASURE(), ISSELECTEDMEASURE(), and other similar functions will already get them explicitly measured from the first Calculation Group. So, the conditions that would be created would always be met or never not met.

A possible solution lies in a trick. First, we can block the first Calculation Group from working with another measure before we tell it (see the condition in the first image). So, if there is a collision between Calculation Group #1 and a measure that will be used within the conditional formatting, then nothing will happen. The second step is to create a Calculated Column in Calculation Group #1, which will contain one singular value that will be the same for all rows or Items. This value can be, for example, the value 1. If we then use the trick CALCULATED([blank], TREATAS({1}, ‘CG#1’[Column])) within the newly emerging Calculation Group #3, we achieve that Precedence 3 will call Precedence 1 and start the calculation from again. So #1 will be followed by #2 and #3, where it has to stop, so you need to make use of the condition that the calculation adjustment in #3 will only be made at a certain point in time, which is when it comes into contact with a measure for conditional formatting. The result of that calculation can be saved as a variable and even used as part of creating an auxiliary column in ADDCOULUMNS(), which we can save in a variable and then use.

Updated Measures and Calculation Groups Calculation Group #3 and Calculated Column and New measure

If I applied a new measure to the visual but not the mentioned Calculation Group #3, the chart would automatically change to black.

Bar chart with FX Bar chart with applied Conditional Formatting

When applying the mentioned Calculation Group #3, the gradient will also be applied. Additionally, using the automatic ORDINAL column from Calculation Group #2, the colors within the gradient will change if TOP or BOTTOM is selected. It is essential, however, that the slicers for #1 and #2 be set so that they are single-selection slicers. In this way, it will always be achieved that their values will be selected, and simultaneously, multiple simultaneous selections will not collide.

Bottom Chart With FX Custom Gradient-based on Measure from Calculation Group #1 and selection from Calculation Group #2 (BOTTOM)

So, if the user changes the value to TOP, the gradient will also change. As mentioned, this graph can also be extended with a Range Parameter, which would allow users to choose how many values they want to display.

Top Chart with FX Custom Gradient-based on Measure from Calculation Group #1 and selection from Calculation Group #2 (TOP)

Combining Calculation Groups
Older post

Power BI Templates

Calculation Groups are a powerful feature in Power BI that allows you to create reusable calculations.