topic Re: RANKX fails with more rows in DAX Commands and Tips

RANKX fails with more rows

lemaribdb — Mon, 06 May 2024 10:52:12 GMT

Hi guys! I have simple RANKX DAX:

1. Amount RANKX = RANKX( ALLSELECTED('DIM_CUSTOMER'[Name]), '1. X_Measures[Amount],, DESC, Dense)

It all works, I put Customer Name as Row, this measure together with Amount in values, there is a ranking visible, all works nicely.
But as soon as I add another row from different table under the Customer Name (for example Contract Number), it immediatly fails saying that it exceeded all available resources.

I assume that this calculation tries to rank further on the next row too? Can I somehow change this measure to behave correctly?
When I do it the default way (Using the built in filters, putting customer name then selecting TopN option and Amount as data), it works perfectly no matter how many drilldown are there. But the TopN is fixed there, thus my workaround.

Re: RANKX fails with more rows

lbendlin — Mon, 06 May 2024 18:50:49 GMT

Move the measure outside the RANKX so that it will be computed only once.

Re: RANKX fails with more rows

123abc — Tue, 07 May 2024 07:40:27 GMT

Here are a few approaches you can try to address this issue:

Optimize the DAX expression: Ensure that your DAX expression is as optimized as possible. Sometimes, small changes in the DAX expression can significantly improve performance. For example, you could check if there are any unnecessary filters or columns being included in the calculation.
Use context transition: You can try using context transition functions like CALCULATE or FILTER to evaluate the RANKX expression within a smaller context, which might improve performance. For example, you could try something like:
DAX

Amount RANKX = RANKX( FILTER(ALLSELECTED('DIM_CUSTOMER'[Name]), HASONEVALUE('DIM_CUSTOMER'[Name])), '1. X_Measures[Amount], DESC, Dense )
This limits the calculation of RANKX to only the rows where 'DIM_CUSTOMER'[Name] has a single value.

Re: RANKX fails with more rows

lemaribdb — Tue, 07 May 2024 08:08:02 GMT

This measure is already computed outside.

Re: RANKX fails with more rows

lemaribdb — Tue, 07 May 2024 08:27:54 GMT

I've tried using IF(ISINSCOPE()) but seems it only works for rows "on top" of the one we do ranking on, the ones below still get some ranks, thus I imagine the resource issue. Still looking for solution.