Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have the following data table in Power BI with a sequence by acct with highes value to lowest value:
Acct | LocationKey | Value | Type | Seq |
1 | AAB | 300 | PH | 1 |
1 | AAC | 550 | HE | 2 |
1 | AAD | 700 | PH | 3 |
1 | AAE | 788 | HE | 4 |
1 | AAF | 980 | PH | 5 |
2 | BBA | 450 | HE | 1 |
2 | BBB | 480 | PH | 2 |
2 | BBC | 700 | PH | 3 |
If i place the data in a table in Power BI and filter by Type = "PH", i get this:
Acct | LocationKey | Value | Type | Seq |
1 | AAB | 300 | PH | 1 |
1 | AAD | 700 | PH | 3 |
1 | AAF | 980 | PH | 5 |
2 | BBB | 480 | PH | 2 |
2 | BBC | 700 | PH | 3 |
What I want is a new measure that will re-rank the sequence based on the filter like this:
Acct | LocationKey | Value | Type | Seq |
1 | AAB | 300 | PH | 1 |
1 | AAD | 700 | PH | 2 |
1 | AAF | 980 | PH | 3 |
2 | BBB | 480 | PH | 1 |
2 | BBC | 700 | PH | 2 |
thanks
Scott
Solved! Go to Solution.
Hi @scabral
Please refer to attached file with the solution
Rank =
IF (
HASONEVALUE ( 'Table'[LocationKey] ),
RANKX (
CALCULATETABLE (
VALUES ( 'Table'[LocationKey] ),
ALLEXCEPT ( 'Table', 'Table'[Acct], 'Table'[Type] )
),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[LocationKey] )
),,
ASC,
Dense
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Please also try this measure expression, that should also work when there is no external filter on Type (i.e., match your original sequence #s).
Acct Rank =
RANKX (
CALCULATETABLE (
SUMMARIZE ( T5, T5[Type], T5[LocationKey] ),
ALLSELECTED ( t5 ),
VALUES ( T5[Acct] )
),
CALCULATE ( SUM ( T5[Value] ) ),
,
ASC
)
Pat
Hi @scabral
Please refer to attached file with the solution
Rank =
IF (
HASONEVALUE ( 'Table'[LocationKey] ),
RANKX (
CALCULATETABLE (
VALUES ( 'Table'[LocationKey] ),
ALLEXCEPT ( 'Table', 'Table'[Acct], 'Table'[Type] )
),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[LocationKey] )
),,
ASC,
Dense
)
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
26 | |
23 | |
12 | |
11 | |
10 |