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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.