RankX by group occurrance

[Anonymous]

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

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.

Any help would be appreciated. Thank you so much!

[tamerj1 · Accepted solution]

@Anonymous 

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

@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

[tamerj1]

Hi @Anonymous 

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

[Anonymous]

Hi @tamerj1 -

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

Example:

LOA_FSID	CreateDate	Index per LOA	Responsibility	Responsibility Index (current)	Responsibility Index (desired)
540-18653	11/10/2022 10:06:48 PM	1	PCR	0	1
540-18653	11/17/2022 7:44:08 PM	2	Enroller	1	2
540-18653	11/17/2022 9:42:49 PM	3	PCR	1	3
540-18653	12/5/2022 4:54:27 PM	4	PCR	1	3
540-18653	12/8/2022 6:03:37 PM	5	Enroller	3	4
540-18653	12/30/2022 2:11:07 PM	6	PCR	2	5
540-18653	1/5/2023 10:55:21 AM	7	PCR	2	5
540-18653	1/9/2023 10:37:26 AM	8	PCR	2	5
540-18653	1/13/2023 1:57:17 PM	9	Enroller	6	6
540-18653	1/13/2023 8:19:28 PM	10	PCR	3	7

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!