RankX on a table with multiple filters

[DK-C-87]

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 ?

[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()
)

[DK-C-87]

Your measure returns this for me:

Do you have a suggestion to why this is ? 

[Bibiano_Geraldo]

Can you please share a sample file with no sensitive information?

Your screenshot is not whowing entire table.

You can upload a sample file in onedrive or other cloud and share the link for download here in comments.

[DK-C-87]

Sorry, I can't share sample file. 
I am filtering on 3 columns and are only showing 1 of these three filtret columns in the visuals table - could it be the problem here ? 
Maybe the measure should summarize first and then rank on that ? 

[danextian]

Are you using the process column in your viz?

[DK-C-87]

You mean my visual 🙂
Yes, I am using the Proces column in my visual - it is the first column in the visual. 

[danextian · Accepted solution]

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?

[DK-C-87]

Things to check:

• are you using just the column specified in the rankx measure or are there more columns?
  • This question did it for me 🙂
• is the expression the rank is based on returning the correct value?
  
  Thank you so much for your help !

[Kedar_Pande]

@DK-C-87 

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 ?

[danextian]

Hi @DK-C-87 

The rank is calculated in the context of other selected rows in the ALLSELECTED(ProcesGBS) table. Rank is supposed to be applied to a column and not a table.

[DK-C-87]

If 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 :