Forum Discussion
Helper IRankX on a table with multiple filters
I need this measure to give me a ranking from 1-21 in my visual, which it dosen't do at the moment
Measure =
IF(HASONEFILTER(ProcesGBS[Proces]),
RANKX(ALLSELECTED(ProcesGBS),
CALCULATE(SUM(ProcesGBS[Timer Norma RAW CM (ProcesID)]),
FILTER(ALL(Stamdata), Stamdata[Plant] <> BLANK()))),
BLANK()
)My table visual
I'm using ALLSELECTED because I have multiple filters set up specific for this table visual.
The table only shows these 21 rows in the visual, but it looks like the rankx measure catches more and therefore gets me as much as ranking 1730
Can anyone crack this mistory ?
In the screenshot below I am using the following measures. Notice that evaluated correctly.
Total Revenue ALLDATES 2023 = CALCULATE ( [Total Revenue], FILTER ( ALL ( Dates ), Dates[Year] = 2023 ) ) RANK = RANKX ( ALL ( Category[Category] ), [Total Revenue ALLDATES 2023], , DESC, DENSE )Now, if i add another column, the rank for the column in rankx and the additional column is evaluated differently.
Things to check:
- are you using just the column specified in the rankx measure or are there more columns?
- is the expression the rank is based on returning the correct value?
12 Replies
DK-C-87If I change the allselected to a column like this :
IF(HASONEFILTER(ProcesGBS[Proces]), RANKX(ALLSELECTED(ProcesGBS[Proces]), CALCULATE(SUM(ProcesGBS[Timer Norma RAW CM (ProcesID)]), FILTER(ALL(Stamdata), Stamdata[Plant] <> BLANK()))), BLANK() )
Then I get this result :
- Kedar_Pande
Super User
Here's a refined measure that you can use:
Measure =
IF(
HASONEFILTER(ProcesGBS[Proces]),
RANKX(
FILTER(
ALLSELECTED(ProcesGBS),
NOT(ISBLANK(SUM(ProcesGBS[Timer Norma RAW CM (ProcesID)])))
),
CALCULATE(
SUM(ProcesGBS[Timer Norma RAW CM (ProcesID)]),
FILTER(ALL(Stamdata), Stamdata[Plant] <> BLANK())
),
,
DESC,
DENSE
),
BLANK()
)Let me know if you have any further questions or need additional adjustments!
💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn- DK-C-87
Your measure returns this for me :
A small different, but still not solved - do you have a suggestion ?
- Bibiano_Geraldo
Hi DK-C-87 ,
To ensure that the ranking is limited to the 21 rows shown in your visual, you can adjust your measure to better respect the current filter context.Please consider this updated DAX and let me know if its all ok:
Measure = IF( HASONEFILTER(ProcesGBS[Proces]), RANKX( ALLSELECTED(ProcesGBS[Proces]), CALCULATE( SUM(ProcesGBS[Timer Norma RAW CM (ProcesID)]), FILTER( ALL(Stamdata), Stamdata[Plant] <> BLANK() ) ), , DESC, DENSE ), BLANK() )
