Trying to Understand CALCULATE and FILTER in DAX

Question

I am trying to play around with FILTER and CALCULATE DAX expressions. I would like to find out the no of distinct orders which have products whose price is less than the average cost of the category: Bikes. First I am creating a Measure to calculate the average cost of Bikes, which is 913.61

avg product cost bikes =
CALCULATE (
    AVERAGE ( AW_Product_DIM[ProductCost] ),
    AW_Product_Categories_DIM[CategoryName] = "Bikes"
)

Next, I am creating a measure to calculate the total no of orders.

Total Orders =
DISTINCTCOUNT ( AW_Sales[OrderNumber] )

Next, I have a CALCULATE Function with FILTER to calculate the total number of orders, which have products whose cost is less than the average cost of all products which is 913.61.

Sales less than Bike's avg =
CALCULATE (
    [Total Orders],
    FILTER (
        AW_Product_DIM,
        AW_Product_DIM[ProductCost] < [avg product cost bikes]
    )
)

Can you please help me understand why I am not getting any value in the Sales less than Bike's avg column? All the products in Accessories and Clothing category are less than the Bike's avg cost which is 913.61. So there should be 16983 in sales less than Bike's avg for Accessories and 6976 in Clothing. All the Bikes are more than 1000, so that should be empty.

Please refer to

Which has the Matrix view that I am trying to Visualize.

score 2 · Accepted Answer · answered Dec 20 '20 at 17:02

2

A measure is calculated within the context it is called from. When you call a measure inside of a FILTER (or any iterator function), it's calculated within the row context of the table you are iterating through and can be a different value for each row.

One way to remedy this is to calculate it once outside the FILTER and reuse that value for each row that FILTER iterates over like this:

Sales less than Bike's avg =
VAR AvgBikeCost = [avg product cost bikes]
RETURN
    CALCULATE (
        [Total Orders],
        FILTER (
            AW_Product_DIM,
            AW_Product_DIM[ProductCost] < AvgBikeCost
        )
    )

answered Dec 20 '20 at 17:02

Alexis Olson

38,724
7
42
64

This is exactly what I am trying to achieve. Since, I am learning PowerBI, I would like to know is there any way else we could achieve this results, Like using a combination of DAX functions such as CALCULATE and REMOVEFILTERS etc ? – chexxmex Dec 22 '20 at 17:23
It's possible to adjust the context within FILTER to make it work but that would be far less efficient than only computing the value once. – Alexis Olson Dec 22 '20 at 17:27
Yes, Can you show me how ? Please, Only for understanding purposes – chexxmex Dec 22 '20 at 21:17
Adding `ALL ( AW_Product_Categories_DIM )` (or `REMOVEFILTERS` instead of `ALL`) as another argument in the `CALCULATE` to your first `avg` measure might do the trick. I'd need to see the model relationship diagram to know for sure though. – Alexis Olson Dec 23 '20 at 14:19

Trying to Understand CALCULATE and FILTER in DAX

1 Answers1