filter overwriting behavior of CALCULATE

[hemanth4pbi]

I am not able to understand how nested CALCULATE statements overwrite the outer filter, in particular, one scenario where column overwrites the outer table filter:  Consider the following visual and measures. Model : Contoso from DAX book

 

1) Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

2) Audio only Sales = CALCULATE ( [Sales Amount], 'Product'[Category] = "Audio" )

3) Nested CALC TABLE-FILTER FALSE =

CALCULATE (

    CALCULATE ( [Sales Amount], 'Product'[Category] = "Audio" ),

    FILTER ( ALL ( 'Product' ), FALSE () )

)

4) Nested CALC COLUMN-FILTER FALSE =

CALCULATE (

    CALCULATE ( [Sales Amount], 'Product'[Category] = "Audio" ),

    FILTER ( ALL ( 'Product'[Category] ), FALSE () )

)

I am unable to understand why the measure Nested CALC TABLE-FILTER FALSE is returning the blank while the measure Nested CALC COLUMN-FILTER FALSE is returning Audio sales.  I expect Nested CALC TABLE-FILTER FALSE should return Audio sales. Can you please help me to understand this behavior?

[hemanth4pbi]

@CNENFRNL  consider the query start at line 40, even though we set the filter context as empty, dax is returning product keys using ALL. PRODUCT[CATEGORY] = "Audio" is translated into

FILTER(ALL( PRODUCT[CATEGORY]),PRODUCT[CATEGORY] = "Audio").

@tamerj1 see the queries starting at line 25 and line 33, when we set the filter context to empty VALUES is returning empty list but ALL is still returning all values in that column.

[CNENFRNL]

What you want to say? Or you mean to teach me something new?

[hemanth4pbi]

Not at all. I am sorry if you feel like that. 

your point but 'Product'[ID] = BLANK() DOES still exist, as well as any other filters on other columns of 'Product'. You said Product[ID] = BLANK() still exist, I was trying to show you Product[ID] is not blank even thogh we set the filter on Product table is empty as you can see in the query at line 40.

[CNENFRNL]

(at line 40) You expect ALL() returns all the visible rows in the current filter context; but it's NOT the case for ALL(). It's now used as a top-level table expression, returning all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Instead, you should use,

CALCULATETABLE (
    VALUES ( 'Product'[ProductKey] ),
    FILTER ( ALL ( 'Product' ), FALSE () )
)

[CNENFRNL · Accepted solution]

First of all, presume that 'Product' table consists of other columns on top of 'Product'[Category], say 'Product'[ID].

Nested CALC TABLE-FILTER FALSE =
CALCULATE (
    CALCULATE ( [Sales Amount], 'Product'[Category] = "Audio" ),
    FILTER ( ALL ( 'Product' ), FALSE () )
)

Inner filter 'Product'[Category] = "Audio" overrides part of outer filter, ie 'Product'[Category] = BLANK(), but 'Product'[ID] = BLANK() DOES still exist, as well as any other filters on other columns of 'Product'.

[hemanth4pbi]

Thanks for the help. CALCULATE is behaving as per your explanation.

[tamerj1]

Hi @hemanth4pbi 

because in (4) both the inner and outer CALCULATE filters are filtering the same column. Therefore, the inner CALCULATE replaces the outer CALCULATE filter and the result is "Audio". 

While number 3 filters all the products (another column with different granularity). This filter which is a empty table, is not replaced by the inner filter rather it is intersected with it and the interaction between the two filters is an empty table that returns blank.