DAX 'ALL' Function

[dicken]

Hi,
I have a question regarding the ALL function, in MS DOCS they have the construction,

Titel :=
SUMX ( ResellerSales_USD, ResellerSales_USD[SalesAmount_USD] )
    / SUMX ( ALL ( ResellerSales_USD ), ResellerSales_USD[SalesAmount_USD] )

But if you use ALL is returns a table of distinct values and removes filters so
eg SUMX( ALL( Table, Table Numbers),Table Numbers ) where the numbers are 1, 2,, 2, 3, 4 would see 1,2,3,4 so I don't see how this can
be correct? What am I missing?

Richard.

 

[GraH]

ALL simply returns all rows and removes the filter context. It does not create a distinct table.

Taken from MS documentation:

The following table describes how you can use the ALL and ALLEXCEPT functions in different scenarios.

Function and usage	Description
ALL()	Removes all filters everywhere. ALL() can only be used to clear filters but not to return a table.
ALL(Table)	Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario.
ALL (Column[, Column[, …]])	Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario.
ALLEXCEPT(Table, Column1 [,Column2]...)	Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

 

[dicken]

ALL simply returns all rows and removes the filter context. It does not create a distinct table.

Taken from MS documentation:

The following table describes how you can use the ALL and ALLEXCEPT functions in different scenarios.

Function and usage	Description
ALL()	Removes all filters everywhere. ALL() can only be used to clear filters but not to return a table.
ALL(Table)	Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario.
ALL (Column[, Column[, …]])	Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario.
ALLEXCEPT(Table, Column1 [,Column2]...)	Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

Well that's what I thought , but if I put into DAX Studio

It returns the following table which is a list of distinct values?

Richard

 

[GraH]

I'm not familiar with DAX studio.
I know DAX optimizes the table by only storing distinct values and keeping the indexes where the value lives in the table (this is only 1 of the ways to optimize the table). Might this play here?

 

[dicken]

ALL simply returns all rows and removes the filter context. It does not create a distinct table.

Taken from MS documentation:

The following table describes how you can use the ALL and ALLEXCEPT functions in different scenarios.

Function and usage	Description
ALL()	Removes all filters everywhere. ALL() can only be used to clear filters but not to return a table.
ALL(Table)	Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario.
ALL (Column[, Column[, …]])	Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario.
ALLEXCEPT(Table, Column1 [,Column2]...)	Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

After further further experimentation,
if you put allTable:=SUMX(ALL(Table1),Table1[unit]) you will get the correct result
but if you put allUnits:=SUMX(ALL(Table1[unit]),Table1[unit]) you will get a sum of the distinct values

Richard

 

[dicken]

I'm not familiar with DAX studio.
I know DAX optimizes the table by only storing distinct values and keeping the indexes where the value lives in the table (this is only 1 of the ways to optimize the table). Might this play here?

Maybe,
I don't know if you've tried it, but if not create a two column table, one column with some numbers,
and try putting in SUMX ( ALL ( Table name) , Table Numbers )
and SUMX ( ALL( Table Name ,Colum Numbers ), Table Numbers)

and you'll see when the ALL( Table , Column) is used it is a distinct list that is fed to SUMX, but when it is just the Table
The entire list of values is summed.
I shall investigate further.

Richard.

 

[GraH]

Curious to see if the Italians have explained this already.
And thanks for sharing your tests.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might displ

www.sqlbi.com

 

[dicken]

LOL, they must have.

RD

 

[dicken]

Curious to see if the Italians have explained this already.
And thanks for sharing your tests.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might displ

www.sqlbi.com

Hi
Just been re watching mike Girvin MSPTDA 18, which I knew had something to say
and both VALUES and ALL return a unique list.
Richard

 

[RoryA]

It's pretty logical isn't it? Why would you want/need duplicated values in a specific column returned for a filter context?