topic Re: Problem when creating a ranking using several tables in Power BI DAX in DAX Commands and Tips

Problem when creating a ranking using several tables in Power BI DAX

Anonymous — Thu, 18 May 2023 17:21:27 GMT

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

And this is the formula that I've created:

Ranking Revenue =

VAR RevenuePerMonth =

SUMX('sales', 'sales'[quantity] * RELATED('products'[price]))

RETURN

RANKX(

FILTER(

ALL('datedim'),

'datedim'[MonthNo] = MAX('datedim'[MonthNo]) ||

'datedim'[Year] = MAX('datedim'[Year])

RevenuePerMonth,

ASC,

Dense

)

However, I'm getting always the same value when I create the table chart using the year and monthNo from the datedim table:

What is missing or what is wrong?

Re: Problem when creating a ranking using several tables in Power BI DAX

johnt75 — Fri, 19 May 2023 09:17:46 GMT

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.

Re: Problem when creating a ranking using several tables in Power BI DAX

Anonymous — Fri, 19 May 2023 14:25:06 GMT

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?

Re: Problem when creating a ranking using several tables in Power BI DAX

johnt75 — Fri, 19 May 2023 14:29:57 GMT

In 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.