Rank within group

[Everton]

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

[Everton]

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

I really only want these records:

[tamerj1]

@Everton 
I fixed already in my original reply

[Everton]

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. 

[tamerj1 · Accepted solution]

@Everton 
You mean like this?

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

[Everton]

@tamerj1 , perfect thank you!

[tamerj1]

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