Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rank by Date & Time

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,

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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 )
    )

    Rank-by-Date-Time

     

    Best Regards,
    Dale

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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 )
    )

    Rank-by-Date-Time

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Try this measure

     

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

     

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    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's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Write these measures

      Start = SUM(Data[SchedStartTime])

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

      Hope this helps.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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 )
        )
  • Fraz707's avatar
    Fraz707
    Helper I
    6 years ago

    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