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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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