Hi
I’m using the Contoso model where 2 measures produce different result and would like help in understanding why that’s the case.
Measure # 1 (with the result expected) - TOPN uses VALUES ( ‘Product’[Product Name] )
Measure # 2 (not the expected result) - TOPN uses the ‘Product’ table
Thanks
Manoj
Hi,
I try to tell you if that make sense
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 .
Hope i make some sense here.
Thanks,
Anuarg
Hi Anurag
Thanks for responding, but I’m not following you.
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,
I’m keen to understand why the result of TOPN differs without the added CALCULATETABLE.
Thanks
@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.
Thanks
Hi ,
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
Thanks,
Anurag
@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.
This article will help you to understand.
Context Transition and Expanded Tables
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.
Hi Antriksh
I’ve simplified the second table expression of GENERATE to try help understand the interplay between row context / filter context / context transition.
Table # 1
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
Table # 2
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
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.
Table # 4
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
Table # 5
Look forward to your feedback / comments
Thanks
Thank you for providing more information @M_K. Bumping this post for more visibility.
Hi @M_K! We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.
We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.
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!
