DAX ALL* functions in CALCULATE - are they always top level functions?

[Regiso]

I am studing the Definitive Guide to DAX and have a question about the ALL* functions used in CALCULATE. Are they always top level functions - meaning, they will always act as CALCULATE modifiers instead of table functions? Thank you!

[Greg_Deckler · Accepted solution]

Not sure what you mean. You can use New Table = ALL('Table') to return a table. But, ALL is generally used as a filter modification.

[amitchandak · Accepted solution]

Refer :

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

[dax · Accepted solution]

Hi @Regiso , 

"All" function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. You could refer to the-all-function-in-dax  and this video  or @amitchandak 's link for details.

By the way, you also could inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Regiso]

Thanks @dax zoe and @Greg_Deckler for your comments. Here are some more specifics. I am working through the Definitive Guide to DAX chapter 5. In several places the authors refer to "top level functions", but there is not index or definition of a top level function. Chapter 5 is the hardest in the book and I know that I will need to reread it a few times before it sinks in. In the meantime I am clinging to any obvious patterns I can, and one of those is trying to understand what a "top level function" means. Here is the specific context: Using the Contoso File:

ColorBrandSales := S

UMX ( KEEPFILTERS ( ALL ( 'Product'[Color], 'Product'[Brand] ) ),

[Sales Amount] )

"The presence of KEEPFILTERS as the top-level function used in an iteration forces DAX to use KEEPFILTERS on the implicit filter argumentsadded by CALCULATE during a context transition." p 169

Later (p171) "As a rule, ALL* functions are CALCULATE modifiers when used as top-level functions in CALCULATE filter arguments."

So, my newbie question is, when ALL* show up in CALCULATE are they always top-level functions, or, are they ALWAYS modifiers?

Thanks - stay well!

[marcorusso · Accepted solution]

Top-level function means that a function is the first in an expression.

If you have multiple arguments, each argument might have a top-level function in case the expressions starts with a function call.

Because ALL used as a top-level function in an argument filter is a modifier (it is not a modifier in all the other cases), Microsoft introduced REMOVEFILTERS that should be used instead of ALL in CALCULATE to avoid confusion.

-- ALL is a top-level function for the first argument of SUMX
SUMX ( ALL ( table[column] ), table[column] * 2 )

-- ALL is a top-level function for the first argument of FILTER
SUMX ( FILTER ( ALL ( table[column], TRUE ), table[column] * 2 )

-- ALL is a top-level function for filter
CALCULATE ( [m], FILTER ( ALL ( table[column] ), TRUE ) )

-- ALL is a top-level function for the second argument of CALCULATE
-- **in this case** ALL is a modifier
CALCULATE ( [m], ALL ( table[column] ) )

-- REMOVEFILTERS is a top-level function for the second argument of CALCULATE
-- REMOVEFILTERS can be used only as a modifier
CALCULATE ( [m], REMOVEFILTERS ( table[column] ) )

[Greg_Deckler]

Well, probably the easiest way to get clarification is to simply ask @marcorusso 

[amitchandak · Accepted solution]

Refer :

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

[Regiso]

Thank you for the super link amitchandak...it will take some time for me to work through it - I am slogging my way through chapter 5 so expanded tables are beyond my pay grade right now. The first part is helpful.

[dax · Accepted solution]

Hi @Regiso , 

"All" function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table. You could refer to the-all-function-in-dax  and this video  or @amitchandak 's link for details.

By the way, you also could inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

Not sure what you mean. You can use New Table = ALL('Table') to return a table. But, ALL is generally used as a filter modification.