topic Re: RANK within group when filtering in DAX Commands and Tips

RANK within group when filtering

scabral — Sat, 03 Dec 2022 17:02:43 GMT

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

Re: RANK within group when filtering

tamerj1 — Sat, 03 Dec 2022 20:23:31 GMT

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

Re: RANK within group when filtering

ppm1 — Sun, 04 Dec 2022 02:45:29 GMT

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

Re: RANK within group when filtering

CNENFRNL — Sun, 04 Dec 2022 07:19:26 GMT