topic Top/Bottom/All on table based on slicer selection in DAX Commands and Tips

Top/Bottom/All on table based on slicer selection

Anonymous — Sun, 26 Mar 2023 05:07:22 GMT

Hi Everyone,

i have a table visual which has Account Name, current period revenue and previous period revenue as values.
I need to give 2 slicers .

1) to select Top/Bottom/All

2) to select number between 2 and 30

Now based on the slicer selections, my table visual should show change.

eg: if I choose All then it should show all Account Name.

if Top and 5 is selected, then table should show top 5 accounts with highest current period revenue. And similar for Bottom as well.

My current solution involves creating the below measure and adding it as Visual level filter :

Top/Bottom Account =

VAR NValue =

SELECTEDVALUE ( 'Drop Down - Account'[Values] )

VAR Ranking_TOP_CUST =

CALCULATE(RANKX (

ALLSELECTED ( 'Sales'[Account Name] ),

[Current Period Revenue],

DESC

),KEEPFILTERS('SalesGenie VN'[Account Name] IN { "Trading/Sub-Dealer" } = FALSE))

VAR Ranking_Bottom_CUST =

RANKX (

ALLSELECTED ( 'Sales'[Account Name] ),

[Current Period Revenue],

ASC

)

VAR Ranking_All_CUST =

RANKX (

ALLSELECTED ( 'Sales'[Account Name] ),

[Current Period Revenue],

ASC

)

VAR Result =

SWITCH (

SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ),

"Top", INT ( Ranking_TOP_CUST <= NValue ),

"Bottom", INT ( Ranking_Bottom_CUST <= NValue ),

"All", 1

)

RETURN

Result

I wanted to check if there is an alternate solution to achieved the same.

Thanks in advance..

Swathi

tamerj1 Greg_Deckler

Re: Top/Bottom/All on table based on slicer selection

Anonymous — Tue, 28 Mar 2023 03:05:41 GMT

Hi Anonymous,

Perhaps you can take a look at the following link that also talked about dynamic top/bottom N if it helps for your requirement:

Solved: Dynamic Top/Bottom N Slicer - Almost have it! - Microsoft Power BI Community
Regards,

Xiaoxin Sheng

Re: Top/Bottom/All on table based on slicer selection

wdx223_Daniel — Tue, 28 Mar 2023 03:58:46 GMT

Top/Bottom Account = SWITCH ( SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ), "All", 1, "Top", IF ( MAX ( 'Sales'[Account Name] ) IN CALCULATETABLE ( VALUES ( 'Sales'[Account Name] ), TOPN ( SELECTEDVALUE ( 'Drop Down - Account'[Values] ), ALLSELECTED ( 'Sales'[Account Name] ), [Current Period Revenue], DESC ) ), 1 ), IF ( MAX ( 'Sales'[Account Name] ) IN CALCULATETABLE ( VALUES ( 'Sales'[Account Name] ), TOPN ( SELECTEDVALUE ( 'Drop Down - Account'[Values] ), ALLSELECTED ( 'Sales'[Account Name] ), [Current Period Revenue], ASC ) ), 1 ) )