Forum Discussion

Anonomous_user's avatar
Anonomous_user
Frequent Visitor
3 years ago
Solved

How to rank categorical data based on time factor

Hello, I have a data frame which has 3 variables i.e. Id, Event and time for each id there can be multiple event happening and for each event date time factor has been added to find out at what time which event happen. My data frame looks like this as below.

 

IdEvent              Time(DD/MM/YYYY HH:MM:SS)Output
1event1              4/20/2022 1:09:47 AM1
1event2              4/20/2022 1:12:23 AM2
1event3              4/20/2022 1:13:35 AM3
1event4              4/20/2022 1:13:36 AM4
1event5              4/20/2022 1:13:37 AM5
1event4              4/20/2022 1:15:17 AM4
1event3              4/20/2022 5:56:35 AM3
1event5              4/20/2022 5:56:35 AM5
2event1              4/26/2022 11:19:00 AM1
2event2              4/26/2022 11:20:05 AM2
2event3              4/26/2022 11:20:46 AM3
2event5              4/26/2022 11:20:50 AM4

Added blank column so that there could be space between event and Time column(Ignore)

 

The difference in event can be seconds, minute or hours or days.

 

My Aim is to based on my Id column I need to add rank order to the events based on Date time factor. So for each Id rank order should starts with 1. 

 

And if the event is repetated like for example consider event4 for Id 1 it has been repetated twice so it should pick first category value assigned to it.

 

Any help would be appreciated 

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your message.

    Please check the below picture and the attached pbix file.

     

     

    Expected result CC =
VAR _summarizetable =
    GROUPBY (
        Data,
        Data[Id],
        Data[Event],
        "@time_earliest", MINX ( CURRENTGROUP (), Data[Time(DD/MM/YYYY HH:MM:SS)] )
    )
RETURN
    RANK (
        SKIP,
        _summarizetable,
        ORDERBY ( [@time_earliest], ASC ),
        PARTITIONBY ( Data[Id] )
    )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    HI,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Expected result CC =
RANK (
    SKIP,
    SUMMARIZE ( Data, Data[Id], Data[Event], Data[Time(DD/MM/YYYY HH:MM:SS)] ),
    ORDERBY ( Data[Time(DD/MM/YYYY HH:MM:SS)], ASC, Data[Event], ASC ),
    PARTITIONBY ( Data[Id] )
)

     

    • Anonomous_user's avatar
      Anonomous_user
      Frequent Visitor
      3 years ago

      Hi, Sorry missed 1 part my Expected Output should be same as output column in the table which I have added above. 

      Logic behind the output is "if the event is repetated like for example consider event4 for Id 1 it has been repetated twice so it should pick first category value assigned to it."

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your message.

        Please check the below picture and the attached pbix file.

         

         

        Expected result CC =
VAR _summarizetable =
    GROUPBY (
        Data,
        Data[Id],
        Data[Event],
        "@time_earliest", MINX ( CURRENTGROUP (), Data[Time(DD/MM/YYYY HH:MM:SS)] )
    )
RETURN
    RANK (
        SKIP,
        _summarizetable,
        ORDERBY ( [@time_earliest], ASC ),
        PARTITIONBY ( Data[Id] )
    )