Conditionally clearing active filters (ALL) in CALCULATE

[mistermat]

How can I conditionally include an ALL() statement to CALCULATE()?  Effectively, I want to clear all active filters under certain circumstances.

Here's a simple illustration:

CALCULATE(
    SUM(Transactions[Amount]),
    ALL(Accounts), // Conditionally include
    Accounts[Index] <= MAX(Accounts[Index]) // Conditionally include
)

Here's my attempt (isCumulative is simply a boolean 

CALCULATE(
    SUM(Transactions[Amount]),
    IF( isCumulative, ALL(Accounts),  1 = 1 ) // Error - multiple columns cannot be converted to a scalar value, also not sure about 1 = 1 but I just want to keep the existing filter context in the false condition
    IF( isCumulative, Accounts[Index] <= MAX(Accounts[Index]), Accounts[Index] IN VALUES(Accounts[Index] ) // This works. It's a different way to keep the existing filters in the else condition (but would like feedback on whether this is correct too)
)

The problem I find is that ALL normally returns a table, but specifically in CALCULATE it seems to behave differently.  Please also refer to this article: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

The above example is a simplified version of my real case which has more conditions.  My only solution has been to have multiple nested IFs with multiple CALCULATEs.  I am looking for a better alternative.

Thanks in advance.

[Stachu]

it doesn't work because IF requires the return values to be scalar, not a table
https://docs.microsoft.com/en-us/dax/if-function-dax#parameters

So you don't get the error because of 1=1. It returns TRUE which is scalar, ALL(Accounts) returns a table, which gives an error

I was thinking of a way of doing it with FILTER and variables, but it comes down to having a conditional that can return a table, which to my knowledge is not possible in DAX - both IF and SWITCH can only return scalar

[Anonymous · Accepted solution]

In DAX the ALL* functions, when used as top-level constructs, are really modifiers, not table functions. They remove filters. They do not filter. It's an important distinction.

Secondly, the easiest way to do what you want is to do sth like this:

var __conditionValue = <get the condition>
var __cond1measure = calculate(....)
var __cond2measure = calculate(....)
var __cond3....
...
var __output =
	switch (
		__conditionValue,
		condition1value, __cond1measure,
		condition2value, __cond2measure,
		...,
		"default value"
	)
return
	__output

Since variables are only calculated in DAX on demand, this does not create any issues.

Best

Darek

[mistermat]

Thanks so much for your input.  Are you sure that variables are calculated on demand?  I have been working under the assumption that that wasn't the case, and have been looking for ways to optimize my CALCULATE calls.

[Anonymous]

I've heard this from The Italians, so it must be true at least most of the time. But with DAX Studio you can check it for yourself.

Best

Darek

[mistermat]

Thanks! Going to try this out throughout my code to try to improve performance.