Power BI - rankx for different categories

0
votes

I have the below two columns and the rank one is the desired results. Basically I want to have it to rank DAYS_OLD based on PRIO.

DAYS_OLD    PRIO    RANK
643          ST      1
643          ST      1
643          ST      1
387          2       1
318          HM      1
315          HM      2
295          HM      3
263          2       2
241          ST      2
235          HM      4
235          2       3
232          2       4

You can see in the above that ST has got RANK 1 for value 643 as opposed to 1,2,3. Is this even possible?

The code I have got is this one 

rank = 
RANKX(
    ALL(ADW_DEFECTS), ADW_DEFECTS[DAYS_OLD],
    ,,Dense
)

But I am unable to add a filter for PRIO.

1
powerbidax

1 Answers

0
votes

The code below looks to produce the desired result in a calculated column.

RANK = RANKX(
    SUMMARIZE(
        ADW_DEFECTS,
        ADW_DEFECTS[DAYS_OLD]
    ),
    CALCULATE(
        MAX( ADW_DEFECTS[DAYS_OLD] )
    ),
    CALCULATE(
        SELECTEDVALUE( ADW_DEFECTS[DAYS_OLD] )
    ),
    DESC,
    Dense
)

Result

UPDATED: Using measure

This code works as a measure.

RANK = 
IF (
    ISFILTERED( ADW_DEFECTS[DAYS_OLD] )
    && HASONEVALUE( ADW_DEFECTS[DAYS_OLD] ),
    RANKX(
        CALCULATETABLE(
            VALUES( ADW_DEFECTS[DAYS_OLD] ),
            REMOVEFILTERS( ADW_DEFECTS[DAYS_OLD] )
        ),
        CALCULATE( VALUES( ADW_DEFECTS[DAYS_OLD] ) )
    )
)

The idea is simpler using measure, compared to the one using calculated column. Basically, it is just calculating RANKX of the value of ADW_DEFECTS[DAYS_OLD] column over the all values of ADW_DEFECTS[DAYS_OLD].

In order to ignore duplicated values of ADW_DEFECTS[DAYS_OLD], the column is wrapped with VALUES inside CALCULATETABLE, making RANKX to iterate over unique values of ADW_DEFECTS[DAYS_OLD].

The calculation makes sense only if a single value of ADW_DEFECTS[DAYS_OLD] is visible in the current filter context. Therefore you need IF to check the filter context and return nothing when it does not contain the appropriate filter.

Result will be looking like this:

Result