A Question in Evaluation Context for ALL Function

[Moatasem]

Hello everyone,

I was trying to build a table that displays the top 5 subcategories by sales, I build the dax formula and it worked fine

Top 5 Subcategories 01 = 
var _top_subcategries = TOPN( 5, ALL( 'Product'[ProductSubcategoryName] ), [Offline Sales], DESC )
return 
CALCULATE( [Offline Sales], _top_subcategries, VALUES( 'Product'[ProductSubcategoryName] ) )

After adding the category name to the table, instead of getting the same top 5 subcategories, I got the full list of subcategories.

after a lot of try and error, I updated the formula as below, and the formula gave the expected output.

Top 5 Subcategories 02 = 
var _top_subcategries = TOPN( 5, ALL( 'Product'[ProductSubcategoryName], 'Product'[ProductCategoryName] ), [Offline Sales], DESC )
return 
CALCULATE( [Offline Sales], _top_subcategries, VALUES( 'Product'[ProductSubcategoryName] ) )

My questions

1. Why adding ProductCategoryName to the table led to this issue? 
   here I discovered that, after adding the category to the table, the result of the TOPN function was all the subcategories.
2. Why adding ProductCategoryName to the ALL function solves it?

[Moatasem]

@Nathaniel_C Thanks for your promt reply. it helped me to figure out the missing part while trying to understand the evalulation context "your measure is not filtering that column and so in the visual you get every category".

@CNENFRNL Thanks for the detailed reply, and the links for these concepts.

I will check them, and try to understand how they affect the final filter context for the formulas that I wrote.

The formula that you provided is working as expected, Thanks again.

[CNENFRNL · Accepted solution]

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!

[Nathaniel_C]

Hi @Moatasem ,
When you add the category column, your measure is not filtering that column and so in the visual you get every category. When you add both columns to the ALL() then it returns a table with the whole table that TOPN filters for the top n values, but now the Category column is filtered as well.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel