Power BI - Rankx and filter

Question

I have got below table and want to add calculated column Rank (oldest top-3) that ranks only when Status is "O". Note that **Rank (oldest top-3)**is the desired result.

Status  Days open   Rank (oldest top-3)
C       1   
O       1           4
O       2           3
C       3   
C       4   
C       5   
O       6           2
O       7           1
C       8   
C       9

I have got below code but they do not work for me.

Rank = IF(order[Status] = "C", BLANK(),
          RANKX(FILTER(order, order[Status] = "O"),
                order[Days open], , 1, Dense))

I get top 3 and not the botom one. Also, with filter it filter out any other data. I tried to replace FILTER with ALLSELECTED but it did not work.

Saaru Lindestøkke Saaru Lindestøkke · Accepted Answer · 2018-12-10T12:55:10

Input

I have created a table named order with the following data:

Status  Days open
C       1
O       1
O       2
C       3
C       4
C       5
O       6
O       7
C       8
C       9

Code

Then I have added a calculated column with the following DAX:

Rank = 
IF('order'[Status] = "C", 
    BLANK(),
    RANKX(
        FILTER('order', 'order'[Status] = "O"),
        'order'[Days open], 
        , 
        0, 
        Dense
        )
)

The only difference compared to your DAX (apart from formatting) is that the second to last option of the RANKX function is 0 instead of 1. The documentation of RANKX indicates that 0 ranks the series in descending order.

Power BI - Rankx and filter

2 Answers

Input

Code

Output