cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mistermat Frequent Visitor
Frequent Visitor

Conditionally clearing active filters (ALL) in CALCULATE

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Conditionally clearing active filters (ALL) in CALCULATE

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

5 REPLIES 5
Highlighted
Super User
Super User

Re: Conditionally clearing active filters (ALL) in CALCULATE

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

Stachu Super Contributor
Super Contributor

Re: Conditionally clearing active filters (ALL) in CALCULATE

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mistermat Frequent Visitor
Frequent Visitor

Re: Conditionally clearing active filters (ALL) in CALCULATE

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.

Super User
Super User

Re: Conditionally clearing active filters (ALL) in CALCULATE

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 Frequent Visitor
Frequent Visitor

Re: Conditionally clearing active filters (ALL) in CALCULATE

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 75 members 1,178 guests
Please welcome our newest community members: