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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dkpcr5
Helper II
Helper II

RankX by group occurrance

I have a table below and am trying to rank order each occurrence of change in Responsibility using a calculated column.

 

As you can see, the code is counting 1 for each instance of a unique occurrence of a Responsibility (so the first time "PCR" appears, it says 1, the second time, 2, etc., and the first time "Enroller" appears, it says 1, the second time, 2, etc...)

dkpcr5_0-1674062710920.png

 

Here's the DAX generating this result:

RANKX(
    FILTER(
        ALL(LOA_Logs),
        LOA_Logs[LOA_UID] = EARLIER(LOA_Logs[LOA_UID])
        && LOA_Logs[Responsibility] = EARLIER(LOA_Logs[Responsibility])),
    LOA_Logs[Index per LOA],
    ,ASC,Dense)

 

Instead, what I'd like it to generate is a unique index each time the Responsibility changes. So ideally, this table would return the numbers in green. Each rank changes as the Responsibility changes.

dkpcr5_1-1674062793362.png

 

Any help would be appreciated. Thank you so much!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@dkpcr5 

Actually the dax for the index2 column is just fine and works as intended. But I just realized that the measure is missing something propably dropped by mistake. Please try

Responsibility Set Date =
MINX (
CALCULATETABLE (
LOA_Logs,
ALLEXCEPT (
LOA_Logs,
LOA_Logs[LOA_UID],
LOA_Logs[Responsibility],
LOA_Logs[Index2]
)
),
LOA_Logs[CreateDate]
)

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

@dkpcr5 

Actually the dax for the index2 column is just fine and works as intended. But I just realized that the measure is missing something propably dropped by mistake. Please try

Responsibility Set Date =
MINX (
CALCULATETABLE (
LOA_Logs,
ALLEXCEPT (
LOA_Logs,
LOA_Logs[LOA_UID],
LOA_Logs[Responsibility],
LOA_Logs[Index2]
)
),
LOA_Logs[CreateDate]
)

That's helpful for the Responsibility Set Date column, but is there a way to get the index column to produce the results requested below?

@tamerj1 I take that back - adding LOA_Logs[Responsibility] into the ALLEXCEPT solved it for the date field and got me to the desired end result. Thanks again for all your help!

dkpcr5
Helper II
Helper II

@tamerj1 This is related to the post you helped solve yesterday: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Help-getting-latest-date-for-earlier-value/m-...

(Thanks again for that!)

 

I ran into a scenario today that didn't fit with the previous solution for the Responsibility Index column. Here's the result in the current scenario assuming the solution from this previous post:

LOA_Logs[Index per LOA] -
RANKX(
    CALCULATETABLE(
        LOA_Logs,
        ALLEXCEPT(LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Responsibility])
    ),
    LOA_Logs[Index per LOA],
    ,
    ASC,
   Dense
)

Desired output in green

dkpcr5_0-1674063300688.png

 

Hi @dkpcr5 

the results seems to be as expected. What is specifically the problem?

Hi @tamerj1 -

The issue is that the Responsibility Index needs to be sorted in order of Responsibility changes (ie, by CreateDate).

Example:

LOA_FSIDCreateDateIndex per LOAResponsibilityResponsibility Index (current)Responsibility Index (desired)
540-1865311/10/2022 10:06:48 PM1PCR01
540-1865311/17/2022 7:44:08 PM2Enroller12
540-1865311/17/2022 9:42:49 PM3PCR13
540-1865312/5/2022 4:54:27 PM4PCR13
540-1865312/8/2022 6:03:37 PM5Enroller34
540-1865312/30/2022 2:11:07 PM6PCR25
540-186531/5/2023 10:55:21 AM7PCR25
540-186531/9/2023 10:37:26 AM8PCR25
540-186531/13/2023 1:57:17 PM9Enroller66
540-186531/13/2023 8:19:28 PM10PCR37

 

In other words, the desired output is that the ranking continues each time Responsibility changes - not to track the ranking per Responsibility.

 

Hope that helps clarify!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors