Rank

[Anonymous]

Hello everybody!

I hav a issue in DAX and can't resolve.

It's a simple Ranking that reset it's count for every "SET", "Bloq" and "Name", based in the "Date" Column.

Normaly we use a agregate column, like a sum of values, to use as Rank Ruler, but i need use a date field.

The Rank Resets for every Other column change.

My need is to see where is the order of actions taked by time.

Colored in red is the desired result.

Name	Bloq	Set	Date	Rank Needed
Peter Paul	1	Run	17/05/2018	1
Peter Paul	1	Bike	21/05/2018	2
Peter Paul	1	Bike	25/05/2018	3
Peter Paul	1	Bike	06/07/2018	4
Peter Paul	2	Run	12/04/2018	1
Peter Paul	2	Bike	21/05/2018	2
Peter Paul	2	Bike	06/07/2018	3

Thax for the help.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may get it with RANKX Function.For example:

Calculated colum

Column = 
RANKX (
    FILTER (
        Table1,
        Table1[Name] = EARLIER ( Table1[Name] )
            && Table1[Bloq] = EARLIER ( Table1[Bloq] )
    ),
    Table1[Date],
    ,
    ASC
)

Or a measure:

Measure = 
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Name] = MAX ( Table1[Name] )
            && Table1[Bloq] = MAX ( Table1[Bloq] )
    ),
    CALCULATE ( MAX ( Table1[Date] ) ),
    ,
    ASC
)

Regards,

[Anonymous]

@v-cherch-msft ,

Thanks for the reply!

Works like a charm!

[Anonymous]

Can do this much easier in Power Query with GroupBy and then add in index column. But why is this two, shoud it be one since Set is different?

Name	Bloq	Set	Date	Rank Needed
Peter Paul	1	Run	17/05/2018	1
Peter Paul	1	Bike	21/05/2018	2

[Anonymous]

@Anonymous , Thanks for the reply.

I Know is easier to make this in Power Query, but i really need to make this in DAX.

For your questions about the SET, its because I need to know the date of every name and Bloq, but not the Set and rank then.

All this to know if the Person do the SET in the order I predicted.

[Anonymous]

No problem, this will work as a calculated column:

Calc Column Rank = 
Var CurrentBloq= Table3[Bloq]
Var CurrentName = Table3[Name]
Var CurrentDate = Table3[Date]
Return

CALCULATE(
    COUNTROWS( Table3 ),
    FILTER( 
        ALL( Table3 ),
        CurrentBloq = Table3[Bloq]
        && CurrentName = Table3[Name]
        && CurrentDate >= Table3[Date]
    )
)