Rank by Date & Time

[Anonymous]

Hello, 

I have a dataset that I am currently ranking by Date, which is working well. However, I have multiple entries for the same day, so I would like it to assign a rank by looking at both the date & the time. Is it possible to make it look at the time stamp in my date field as well? 

Here is my dataset, how the rank function is currently working and how I WANT it to work in green:

Note my actual dataset has date/time/seconds/ms for each row so each timestamp is unique. This screenshot is of an export i did into excel for display purposes.

Here is my rank function: 

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

If there is anyway to accomplish this using Rank or otherwise, I would be grateful to hear! 

Thanks,

[Fraz707]

Can you tell what modification can we do on this so that the ranking is done taking in acoount the seconds also?, as i tried the mentioned DAX and its working fine till minutes but if two records are only varying by seconds then this query ranks both of them equally. Attached a screen shot for reference, left side is the date and right side is the rank.

Thanks.

@v-jiascu-msft 

[Anonymous]

Hi All - relating to a similar scenario, I have the below table wherein  I want to RANK the 'SchedSTartTime' by each 'Resource' as shown in the 'desired result' column. Your help would be much appreciated. Thanks

[Ashish_Mathur]

Hi,

Write these measures

Start = SUM(Data[SchedStartTime])

Rank = RANKX(ALL(Data[Resource]),[Start])

Hope this helps.

[Anonymous]

Thanks very much. Since i needed the results urgently, I did the below to get to the desired outcome but thanks for your time. its apprecaited.

1 - CONCATENATE(data[SaSchedDate].[Date],data[resource])

2. RankSaBySchedDateTime = VAR d = Data[SaSchedDate]

VAR a = Data[above concatenated field]

RETURN

CALCULATE (

RANK.EQ ( d, Data[SaSchedDate], ASC ),

FILTER ( ALL ( 'Data' ), data[above concatenated field] = a )

)

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

You just need to remove ".[Date]".

Rank =
VAR d = EventSignatures[DateSigned]
VAR a = EventSignatures[ActiveForm_ld]
RETURN
    CALCULATE (
        RANK.EQ ( d, EventSignatures[DateSigned], ASC ),
        FILTER ( ALL ( 'EventSignatures' ), EventSignatures[ActiveForm_ld] = a )
    )

Best Regards,
Dale

[Anonymous]

Oh my goodness, of course! Thank you so much. 

[Ashish_Mathur]

Hi,

Try this measure

=RANKX(ALL(EventSignatures[ActiveForm_Id]),SUM(EventSignatures[DateSigned]))

Hope this helps.