Ranking Of Groups By Device ID and usage time

[Kumar11109]

Dear All, 

I am facing an issue. I need to rank my groups by categorizing them on who used the device first. For example, if group 1 used the device on 5:00 PM on given day and if group B used the device at 5:30 on the same day, then group 1 should be ranked 1 and group 2 should be ranked 2. 

Here is the sample data. Help would be highly appreciated. 

Thank You, 

Kumar Ashwarya  

[Hardik]

hope this helps !

[Kumar11109]

I don't think so it's working. I want the ranking for groups, by time, when they accessed the same device. 

[danextian · Accepted solution]

Hi @Kumar11109,

Try this:

Create a calculated table 

EarliestAccess = 
//creates a calculated table that summarizes device the original table by DeviceID and Group ID then adds a new column that returns the earliest access date of a group per device
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[DeviceId], 'Table'[GroupId] ),
    "Earliest Access", CALCULATE (
        MIN ( 'Table'[StartDateTime] ),
        ALLEXCEPT ( 'Table', 'Table'[GroupId], 'Table'[DeviceId] )
    )
)

Then in that calculated table, create a calculated column for rank.

RANK = 
CALCULATE (
    COUNTROWS ( 'EarliestAccess' ),
    ALLEXCEPT ( 'EarliestAccess', 'EarliestAccess'[DeviceId] ),
    'EarliestAccess'[Earliest Access]
        <= EARLIER ( 'EarliestAccess'[Earliest Access] )
)