Interplay Between No Selection Expression & Measures That Filter Calculation Groups

Did you know that a no selection expression can impact DAX expressions that filter the calculation group, changing their output? While not a bug, this behavior may be unexpected so is something to be aware of.

To explore, we’ll use this setup:

• A Base measure, which simply returns the static value 1.
• Two calculation groups:
  • CGAdd (precedence 10), with a single member that adds 3 to the selected measure: SELECTEDMEASURE() + 3
  • CGMultiply (precedence 20), with a single member that multiplies the selected measure by 2: SELECTEDMEASURE() * 2
• A Main measure, which applies both of the calculation groups to measure Base:

CALCULATE (
    [Base],
    CGAdd[Name] = "Add",
    CGMultiply[Name] = "Multiply"
)

The above, along with a placeholder table, can be set up using the following TMDL script:

createOrReplace
	model Model
		discourageImplicitMeasures

		table CGAdd
			calculationGroup
				precedence: 10
				calculationItem Add = SELECTEDMEASURE() + 3
			column Name
				dataType: string
				sourceColumn: Name

		table CGMultiply
			calculationGroup
				precedence: 20
				calculationItem Multiply = SELECTEDMEASURE() * 2
			column Name
				dataType: string
				sourceColumn: Name
			
		table Fact
			measure Base = 1
			measure Main =
					CALCULATE (
						[Base],
						CGAdd[Name] = "Add",
						CGMultiply[Name] = "Multiply"
					)
			column Col1
				sourceColumn: [Col1]
			partition Fact = calculated
				mode: import
				source = ROW("Col1", 1)

Question: What will the result be when measure Main is evaluated?

This measure applies two calculation groups, so there are two possibilities, depending on which calculation group is applied first:

• If CGAdd is applied before CGMultiply: (1 * 2) + 3 = 5
• If CGMultiply is applied before CGAdd: (1 + 3) * 2 = 8

Since CGMultiply has a higher precedence than CGAdd, it is applied first. The result is 8. So far, so good.

Now, let’s use the new no selection expression feature. Say you always want CGAdd[Name] = "Add" applied to every measure. A no selection expression seems a great way to make this the default, so you modify the calculation group’s definition, adding:

createOrReplace
	table CGAdd
		calculationGroup
			calculationItem Add = SELECTEDMEASURE() + 3
			noSelectionExpression = 
					CALCULATE (
						SELECTEDMEASURE(),
						CGAdd[Name] = "Add"
					)
		column Name
			dataType: string
			sourceColumn: Name
			sortByColumn: Ordinal
		column Ordinal
			dataType: int64
			sourceColumn: Ordinal

However, after making this change, measure Main‘s behavior changes: Instead of outputting the expected 8, it now returns 5. Strange—this is what would be output if the two calculation groups had their order of application reversed. What happened?

For that matter, why does the no selection expression have any effect on measure Main? That measure filters CGAdd. Since there is a selection on it, shouldn’t the no selection expression be ignored and so have no effect in this case?

No. Whether or not to apply the no selection expression is determined before measures are evaluated. A no selection expression is applied if the query that the user/client application submits for evaluation leaves the calculation group unfiltered. What happens inside measures is irrelevant. The decision on whether to apply the no selection expression, and if relevant its application, takes place before any measures are evaluated.

In our case, the no selection expression results in CGAdd being applied before Main is evaluated. Since CGAdd is now being applied first (unlike before the no selection expression, when it was being applied second), the result changes.

Let’s walk through the various transformations in effectively equivalent DAX code:

Say the user submits the following DAX query.

EVALUATE
    { [Main] }

Prior to the reference to Main being evaluated, it is “plugged into” CGAdd‘s no selection expression since CGAdd is unfiltered at this point, like this:

CALCULATE (
    [Main],
    CGAdd[Name] = "Add" // SELECTEDMEASURE() + 3
)

Which, after applying “Add” becomes:

[Main] + 3

Next, expanding Main‘s definition gives us:

CALCULATE (
    [Base],
    CGAdd[Name] = "Add",
    CGMultiply[Name] = "Multiply"
) + 3

Remember that calculation groups have recursion protection that guards against nested recursion: a given calculation item cannot be applied again in a nested context. Since CGAdd[Name] = "Add" has been previously applied (by the no selection expression), attempting to apply it again here (in the body of Main) doesn’t work. Instead the attempt is ignored, effectively reducing the above to:

CALCULATE (
    [Base],
    CGMultiply[Name] = "Multiply"
) + 3

Applying Multiply gives us:

([Base] * 2) + 3

Which, since Base returns 1, translates to:

(1 * 2) + 3 // = 5

The moral of the lesson: A no selection expression is applied not based on whether the calculation group is ever filtered, but rather based on whether it is filtered by the user’s query (so before any measures are evaluated). Setting a no selection expression can change the effective behavior of measures that also filter the same calculation group.

Bonus: Before we leave the topic, keep in mind that sideways recursion is allowed in DAX. That is, while a nested context cannot reapply a previously applied calculation item, it can apply a different calculation item from the same calculation group. The reason that CGAdd[Name] = "Add" in Main is ignored above is because Add has already been applied in a parent context. Instead, if Main filters CGAdd to a different item (supposing another item exists), that other item will still be applied. Again, if you are expecting the no selection expression to kick in only if CGAdd is never, ever filtered, the net effect will be an unexpected result.