Rank Values of one column by filtering on 2nd column in DAX

Question

I'm trying to use the RANKX formula rank the values of one column, but filtered for the value of a second column. In this example, col2 is a simple counter running in ascending value. I'm trying to find item_id's Rank value relative to the col1.

col1    col2
1001    8001
1001    8002
1002    8003
1002    8004
1002    8005

I'd like to figure out a col3 that would read:

col1    col2    col3
1001    8001    1
1001    8002    2
1002    8003    1
1002    8004    2
1002    8005    3

Because that would be the rank of col2 relative to col1.

Rory Rory · Accepted Answer · 2015-04-27T12:29:58

You don't need to use RANKX at all. See the section on EARLIER in the superb http://www.daxpatterns.com/. Add a new calculated column in your table:

=
COUNTROWS (
    FILTER (
        MyTable,
        [col2] <= EARLIER ( [col2] )
            && [col1] = EARLIER ( [col1] )
    )
)

If you do want to use RANKX you can adapt the formula as follows:

=
RANKX (
    FILTER ( MyTable, [col1] >= EARLIER ( [col1] ) ),
    [Col2],
    ,
    1
)

Rank Values of one column by filtering on 2nd column in DAX

1 Answers