topic Re: RankX by group occurrance in DAX Commands and Tips

RankX by group occurrance

Anonymous — Wed, 18 Jan 2023 17:27:33 GMT

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!

Re: RankX by group occurrance

Anonymous — Wed, 18 Jan 2023 17:35:47 GMT

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-p/3027038#M103256

(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

Re: RankX by group occurrance

tamerj1 — Wed, 18 Jan 2023 18:53:18 GMT

Hi Anonymous

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

Re: RankX by group occurrance

Anonymous — Wed, 18 Jan 2023 19:15:26 GMT

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!

Re: RankX by group occurrance

tamerj1 — Wed, 18 Jan 2023 19:49:47 GMT

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

Re: RankX by group occurrance

Anonymous — Wed, 18 Jan 2023 20:24:17 GMT

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?

Re: RankX by group occurrance

Anonymous — Wed, 18 Jan 2023 20:41:46 GMT

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!