Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
@Everton
You mean like this?
RankInGroupTest =
IF (
NOT ISEMPTY ( data ),
RANKX (
CALCULATETABLE (
data,
ALLEXCEPT ( Data, data[ID] )
),
CALCULATE ( SELECTEDVALUE ( data[MostRecentUpdate] ) ),,
DESC,
Dense
)
)
Thanks for your help @tamerj1 . Do you know why there are all these extra records appearing?
I really only want these records:
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.
@Everton
You mean like this?
RankInGroupTest =
IF (
NOT ISEMPTY ( data ),
RANKX (
CALCULATETABLE (
data,
ALLEXCEPT ( Data, data[ID] )
),
CALCULATE ( SELECTEDVALUE ( data[MostRecentUpdate] ) ),,
DESC,
Dense
)
)
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
)
)
User | Count |
---|---|
69 | |
43 | |
21 | |
20 | |
14 |
User | Count |
---|---|
124 | |
41 | |
39 | |
28 | |
24 |