Forum Discussion

Kumar11109's avatar
Kumar11109
Icon for Helper IV rankHelper IV
8 years ago
Solved

Ranking Of Groups By Device ID and usage time

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 d...
Need Help
  • danextian's avatar
    danextian
    8 years ago

    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] )
)
Hardik's avatar
Hardik
Icon for Continued Contributor rankContinued Contributor
8 years ago

hope this helps !

Kumar11109's avatar
Kumar11109
Icon for Helper IV rankHelper IV
8 years ago

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

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

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