Rank Values of one column by filtering on 2nd colu

问题:

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.

回答1:

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
)