Rank if

[PMF999]

Could someone give me the M code or DAX formula to achieve the result in column Rank? Many thanks!

Type       Date             Rank

Type A    01/01/17     1

Type A    01/02/17     2

Type A    01/03/17     3

Type B    01/01/18     1

Type B    01/02/18     2

Type B    01/03/18     3

Type B    01/04/18     4

[themistoklis]

@PMF999

Create a new Column and Measure and add the folowing DAX code:

Rank = 
VAR d = Table[Date]
VAR a = Table[Type]
RETURN
    CALCULATE (
        RANK.EQ ( d, Table[Date], ASC ),
        FILTER ( ALL ( 'Table' ), Table[Type] = a )
    )

[PMF999]

Thanks.

I've tried this..

=CALCULATE(RANK.EQ(Primary_ID[Date],Primary_ID[Date],ASC),FILTER(all(Primary_ID),Primary_ID[Type]=Primary_ID[Type]))

But get the error..

A single value for column 'Date' in table 'Primary_ID' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[themistoklis]

@PMF999

You have to declare the variables outside the DAX formula and then call them inside (as shown on the formula sent). 

They are immutabe that's why i used them.

If you try it as shown, you will see it works this way

[Zubair_Muhammad · Accepted solution]

@PMF999

As a calculated column..you can use

Column =
RANKX (
    FILTER ( Primary_ID, [Type] = EARLIER ( [Type] ) ),
    [Date],
    ,
    ASC,
    DENSE
)

[PMF999]

Perfect! That's the answer thanks!