Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kumar11109
Helper IV
Helper IV

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

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









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

3 REPLIES 3
Hardik
Continued Contributor
Continued Contributor

hope this helps !

Screenshot_7.png

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

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









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors