Forum Discussion
Problem when creating a ranking using several tables in Power BI DAX
Hi team. I'm trying to do this, for each couple year-month I need to create the ranking of my revenue. The rankin must restart everytime the year changes. This is an image of my model A...
Variables in DAX aren't really variables, they're constants, so the RevenuePerMonth variable is only calculated once, it is not recalculated for each year / month combination.
Take your code from the variable declaration and use it to create a new measure, and then reference that measure in your ranking code.
Thanks johnt75 for your help. I have made the modifications and right now the metric is working. However, I would like to understand something. If I write this expression the ranking is ok:
Ranking Revenue Test =
RANKX(
ALL(DateDim[YYYYMM],'DateDim'[MonthNo]),
[Revenue Measure],,
DESC,
Dense
)
But, when I write this one the metric always returns the same value 1:
Ranking Revenue Test =
RANKX(
ALL(DateDim[YYYYMM],'DateDim'[MonthNo]),
SUMX(sales, sales[quantity] * RELATED(products[price] )),,
DESC,
Dense
)
What is happening in the second definition?
johnt75
Super User
Super UserIn the first version context transition is happening because you are calling a measure. In the second it is not happening and so the same value is being calculated for every row. If you wrapped the SUMX inside CALCULATE then it would work OK.