How to rank record by day and by same truck number and desc by created time?

[FrankNY007]

Hi PowerBI Masters:

I have a quetion of how to rank my data in power BI, please see the sample data below:

I

As you can see, in the same day(I have mutiple day's data), one truck may have different records created by different time. In column D I want to created a dimension, which rank the created time by truck number by deliverydate as descding. In SQL, it should be:

  rank( ) over( TRUCKNUMBER,DELIVERYDATE order by DATECREATED desc) as Rank_Index

But I don;t know how to do it in PowerBi, I only need the lastest record for each truck on each day(which means rank=1), can anyone let me know how can I do it in data modeling view(DAX) or even query editor mode(power query)

Thanks

Frank

[FrankNY007]

anyone knows what the solution for that?

[v-juanli-msft]

Hi

rank1 =
RANKX (
    ALLEXCEPT ( Sheet1, Sheet1[number] ),
    CALCULATE ( SELECTEDVALUE ( Sheet1[createdtime] ) ),
    ,
    DESC,
    DENSE
)

Best Regareds

Maggie 

[Vvelarde]

@FrankNY007

Hi, in your sample 1007 Rank 1 (The Latest Record) Should be 22:13?

[FrankNY007 · Accepted solution]

Yes, sorry typing error, I have an solution, but I am not sure if this is the best way?

Rank= RANKX(CALCULATETABLE(mytable,ALLEXCEPT(maytable,vehiclenumber,deliverdate)),mytable[DATECREATED],,DESC,Dense)