topic Rank within group in DAX Commands and Tips

Rank within group

Everton — Wed, 15 Feb 2023 05:47:54 GMT

Hi,

I want to rank within a group. First group by Id then rank by MostRecentUpdate. This is how I want the data to look.

Id	InvoiceNumber	InvoiceDate	InvoiceStatus	Owing	MostRecentUpdate	Rank
1aaae1ed-2c74-ed11-ac20-d8e2df0c6786	28108	5/12/2022 0:00	Paid	$0	4/12/2022 23:40	1
1aaae1ed-2c74-ed11-ac20-d8e2df0c6786	28108	5/12/2022 0:00	Unpaid	$939.30	4/12/2022 23:39	2
8178e2a2-5240-ec11-981f-501ac52e327f	10026	8/11/2021 0:00	Paid	$0	4/12/2022 23:53	1
8178e2a2-5240-ec11-981f-501ac52e327f	10026	8/11/2021 0:00	Part paid	$121.40	8/11/2021 5:13	2
cdf5286c-2d74-ed11-ac20-d8e2df0c6786	28112	5/12/2022 0:00	Paid	$0	4/12/2022 23:44	1
cdf5286c-2d74-ed11-ac20-d8e2df0c6786	28112	5/12/2022 0:00	Unpaid	$120	4/12/2022 23:43	2
de40fdc5-2e74-ed11-ac20-d8e2df0c6786	28123	5/12/2022 0:00	Paid	$0	4/12/2022 23:53	1
de40fdc5-2e74-ed11-ac20-d8e2df0c6786	28123	5/12/2022 0:00	Unpaid	$410	4/12/2022 23:52	2
e0ea3584-cd74-ed11-ac20-d8e2df0c6786	28141	6/12/2022 0:00	Paid	$0	5/12/2022 18:50	1
e0ea3584-cd74-ed11-ac20-d8e2df0c6786	28141	6/12/2022 0:00	Paid	$0	5/12/2022 18:50	1
e0ea3584-cd74-ed11-ac20-d8e2df0c6786	28141	6/12/2022 0:00	Unpaid	$120	5/12/2022 18:49	2
e70bfa9d-2d74-ed11-ac20-d8e2df0c6786	28115	5/12/2022 0:00	Paid	$0	4/12/2022 23:45	2
e70bfa9d-2d74-ed11-ac20-d8e2df0c6786	28115	5/12/2022 0:00	Paid	$0	4/12/2022 23:53	1
e70bfa9d-2d74-ed11-ac20-d8e2df0c6786	28115	5/12/2022 0:00	Unpaid	$151.50	4/12/2022 23:44	3
f08f6a2f-ce74-ed11-ac20-d8e2df0c6786	28146	6/12/2022 0:00	Paid	$0	5/12/2022 18:54	1
f08f6a2f-ce74-ed11-ac20-d8e2df0c6786	28146	6/12/2022 0:00	Unpaid	$323.20	5/12/2022 18:54	1

I have tried to do a measure, but it gives everything a rank of 1:

RankInGroupTest = VAR InvoiceId = SELECTEDVALUE(data[Id]) VAR FilteredInvoices = FILTER(ALL(data),data[Id] = InvoiceId) RETURN RANKX( FilteredInvoices, CALCULATE(SELECTEDVALUE(data[Id])), , DESC, Dense )

I have attached an example pbix file. This has to be a measure because I want the rank to be dynamic upon what the user has selected in the slicers.

See example pbix: https://filebin.net/y5glwphpsdgcf1c2/Billing_Test.pbix

Re: Rank within group

tamerj1 — Wed, 15 Feb 2023 06:11:52 GMT

Hi Everton
Please refer to attached sample file with the solutin

IF ( NOT ISEMPTY ( data ), RANKX ( CALCULATETABLE ( data, ALLEXCEPT ( Data, data[InvoiceDate] ) ), CALCULATE ( SELECTEDVALUE ( data[Id] ) ) & CALCULATE ( SELECTEDVALUE ( data[MostRecentUpdate] ) ),, DESC, Dense ) )

Re: Rank within group

Everton — Wed, 15 Feb 2023 06:14:08 GMT

Thanks for your help tamerj1 . Do you know why there are all these extra records appearing?

I really only want these records:

Re: Rank within group

tamerj1 — Wed, 15 Feb 2023 06:15:04 GMT

Everton
I fixed already in my original reply

Re: Rank within group

Everton — Wed, 15 Feb 2023 06:19:38 GMT

Thank you again tamerj1 . I was wanting to group by Id and rank within that group. I have tried to highlight the groups in the below image:

For example, withing the group with Id "e0ea3584-cd74-ed11-ac20-d8e2df0c6786" there should be rank 1,2,3. Rank 1 will be the latest MostRecentUpdate.

Re: Rank within group

tamerj1 — Wed, 15 Feb 2023 06:38:08 GMT

Everton
You mean like this?

RankInGroupTest = IF ( NOT ISEMPTY ( data ), RANKX ( CALCULATETABLE ( data, ALLEXCEPT ( Data, data[ID] ) ), CALCULATE ( SELECTEDVALUE ( data[MostRecentUpdate] ) ),, DESC, Dense ) )

Re: Rank within group

Everton — Wed, 15 Feb 2023 07:08:36 GMT

tamerj1 , perfect thank you!