powerbi - Why does Sales (CALCULATE/FILTER) not restrict itself to the product color for that row?

Question

DEFINE
    MEASURE Sales[Average Sales Amount] =
        AVERAGEX ( Sales, 'Sales'[Quantity] * 'Sales'[Net Price] )
EVALUATE
ADDCOLUMNS (
    VALUES ( Product[Color] ),
    "Sales", CALCULATE(
        [Average Sales Amount],
        FILTER ( Sales, Sales[Quantity] > 3 )
    )
)

First it Will create the distinct list of products respecting the current filter context (since VALUES is used).

Then it will iterate over each row of the above list and perform the CALCULATE.

CALCULATE i understand adds the current row (Color) to the filter context. So, in the CALCULATE, I am trying to understand why the sales in FILTER doesn't restrict itself to the respective color even though the respective color is in the filter context.

It appears that when the CALCULATE transitions by adding the color (from row context) into the filter context, for some reason the color does not filter the Sales table.

question from:https://stackoverflow.com/questions/65867219/why-does-sales-calculate-filter-not-restrict-itself-to-the-product-color-for-t

Answer 1

For the order of execution of the steps of CALCULATE, first the FILTER is evaluated in the existing filter context, before the context transition.

This means that the Sales table is not yet filtered by the Color.

Then the filter context happens, adding the filter over the currently iterated 'Product'[Color] to the filter context inside CALCULATE.

Eventually the filter over Sales is applied.

Since this is a filter over the whole Sales table, the expanded table is used.

This means that any filer existing over the Sales expanded table is replaced.

The Sales expanded table contains the 'Product' table, since a one to many relationship exists between Product and Sales.

The result is that the filter over Product[Color] set by the context transition is removed.