Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
)
)