CODEWITHSUNDEEP
powerbidax
xlmaster
xlmaster

Reputation: 721

Dax All function inside AverageX function

Can anyone explain me what does ALL function do below inside function. Does it make relationship between 'Sales' table with 'Product' table?

_Sales by Product Column Chart Colour = 

var avgSales = AVERAGEX(
ALL('Product'[Product])

, [Sales Amount]

)

return

IF([Sales Amount] < avgSales, "Dark Blue")

Upvotes: 0

Views: 539

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

OK. ALL is a versatile DAX function. It can be used either as a table function or calculate modifier. Let's analyze the behavior of ALL, first checking your code:

1)) As a Table Function like in your code

Sales by Product Column Chart Colour = 
    var avgSales = AVERAGEX(
    ALL('Product'[Product])      
    ,[Sales Amount]
    )
    RETURN
    IF([Sales Amount] < avgSales, "Dark Blue")

It returns all the unique values of [Product] column on Product table, and for each row, evaluate sales amount measure. It ignores the filter on product column and calculates overall averages of all products. You know the rest starting with if.

2)) As a calculate modifier like in below measure:

Test_Measure = 
CALCULATE(
 SUM(Sales[Amount]),
 ALL(Product)
)

It removes all the filters on all the columns of the product table, and affects the filter context directly.

To summarize, It is important where you use ALL. Depending on it, It reacts differently.

I hope this is enough for you to understand its behavior.

Upvotes: 2

Related Questions