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:
ALL(Accounts), // Conditionally include
Accounts[Index] <= MAX(Accounts[Index]) // Conditionally include
Here's my attempt (isCumulative is simply a boolean
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 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.
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
Did I answer your question? Mark my post as a solution!
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.