A Question in Evaluation Context for ALL Function

Hi, Moatasem , I think this seemingly simple change, in fact, involves at least 3 profound concepts in DAX calculation, namely auto-exist, data lineage and evaluation context. 😂 You might want to study those linked articles I placed for wrapping your head around some sophisticate mechanisms of DAX under the hood.

Let's get back to your measure and break it down,

First

ALL( 'Product'[ProductSubcategoryName], 'Product'[ProductCategoryName] )

materialises a table with all possible combinations in 'Product' due to auto-exist (let's name it "prod").

Secondly,

TOPN filters 'prod' with data lineage being kept, which means 'prod'[ProductCategoryName] and 'prod'[ProductSubCategoryName] reference original 'Product'[ProductCategoryName] and 'Product'[ProductSubCategoryName] respectively with filtered values by TOPN.

Last but not the least,

CALCULATE( [Offline Sales], _top_subcategries, VALUES( 'Product'[ProductSubcategoryName] ) )

2 filter modifiers, namely _top_subcategories and VALUES(...) override initial filter context from the table viz.

Btw, I authored a more flexible measure which you might want to test, if you'd like to add to your report more slicers such as date, region etc.

Top 5 Subcategories 03 =
VAR _top_subcategries =
    TOPN (
        5,
        ALL ( 'Product'[ProductSubcategoryName] ),
        CALCULATE (
            [Offline Sales],
            ALL ( 'Product'[ProductCategoryName] )
        ), DESC
    )
RETURN
    CALCULATE ( [Offline Sales], KEEPFILTERS ( _top_subcategries ) )

Pls update me with the result of performance if you test it.

Enjoy DAX!