In first measure you are calculating [sale Amount] for only Product Name your values function give you table which contain only product name so your sales amount is calculated on the base of product name.

which we want to see in topn

In second scenario :

You are trying to calculate sale for whole table which conation many column which taking the sales to its granule so TopN actually ranking wrong here because it is working on wrong sales amount .

It seems when I force context transition (via CALCULATETABLE) for rows in the first table expression of GENERATE, the result is the same. Please refer extracts below,

@M_K Because Context Transition can only affect the first argument of CALCULATE/CALCULATETABLE, the filter arguments of both aren’t impacted by context transition, when you use 2 CALCULATETABLE, the inner CALCULATETABLE is the first argument of Outer CALCULATETABLE.

Thanks Antriksh - but can you please expand on why the result of TOPN differs when the tables used are different, i.e. Product table vs a single column table with just the product names.

As you applied context transaction forcefully here by adding inner calculate which become first argument for your outer calculate now its performing context transaction filtering the table based on product name and then calculating the sale on which the TOPN works now and then TOPN run on it .

Pls correct me @AntrikshSharma if i went wrong while explaining here

@Anurag Explaining this will take time because it involves understanding that TOPN does 2 operations internally and that [Total Sales] is intiating 2 context transitions and the intial filter context is coming from GENERATE () VALUES (), will write about it later or maybe will make a video.

There are few golden rules in the DAX world, but one for sure is: never filter a table when you can filter a column. In fact, filtering a table requires you to understand expanded tables and their behavior when you use them as a filter parameter in CALCULATE.

I’ve simplified the second table expression of GENERATE to try help understand the interplay between row context / filter context / context transition.

Total number of rows = 2,517 (same as the Product table)

CALCULATETABLE transforms the row context on the first table expression (of GENERATE) into an equivalent filter context before applying it’s filter argument - so there this one product category visible

Filter argument of CALCULATETABLE is VALUES ( ‘Product’ [Product Name] ), a column that does not overwrite the filter over ‘Product’ [Category] in the filter context generated by context transition (not the same column)

Therefore, VALUES ( ‘Product’ [Product Name] ) [the second argument of CALCULATETABLE] returns a list of product names associated with the product category visible in the filter context

And so the second table expression of GENERATE returns a list of product names associated with the product category in the first table expression

Total number of rows = 20,136 (2,517 * 8 product categories)

CALCULATETABLE transforms the row context on the first table expression (of GENERATE) into an equivalent filter context before applying it’s filter arguments - so there this one product category visible

Filter argument of CALCULATETABLE is the ‘Product’ table, which has ‘Product’ [Category] as one of it’s column, and therefore overwrites the filter over ‘Product’ [Category] generated by context transition

Therefore, ‘Product’ [the second argument of CALCULATETABLE] returns a full list of product names in the Product table

And so the second table expression of GENERATE returns a list of all product names in the Product table for each product category, i.e. (2,517 * 8) rows in total once GENERATE has completed it’s evaluation.

Table 3 differs from table 1 with the inclusion of an additional CALCULATETABLE

The first/outer CALCULATETABLE (second table expression of GENERATE) transforms the row context on the first table expression (of GENERATE) into an equivalent filter context - so there this now one product category visible

When the second/inner CALCULATETABLE is initiated there isn’t a row context - but a filter context that shows one product category. And therefore, the filter argument of the inner CALCULATETABLE returns a list of product names associated with the product category visible in the filter context.

result of table 4 differs from table 2 - shows the correct number of rows (2,517)

The first/outer CALCULATETABLE (second table expression of GENERATE) transforms the row context on the first table expression (of GENERATE) into an equivalent filter context - so there this now one product category visible

When the second/inner CALCULATETABLE is initiated there isn’t a row context - but a filter context that shows one product category. And therefore, the filter argument of the inner CALCULATETABLE returns only rows of the ‘Product’ table associated with the product category in the filter context - note there is no ALL - (contrast this with table 5 below which includes ALL, and returns 20,136 rows - similar to table 2)

And so the second table expression of GENERATE returns only the list of product names linked to product category

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!