Power BI Dynamic Filtering

[QTHUYNH]

Hello,

I followed EnterpriseDNA's dynamic instructions to recreate this dashboard from Decisive Data. I'm currently stuck trying to recreate TopX/BottomX in the filters. I can show Top 3, 5, 10, etc., but I cannot get the graph to display Bottom.

My selection criteria are: 1st - Revenue and Profits, 2 - Items and Customers, 3 - Top 5 and Bottom 5 (I'm having issues with this one).

Formula for Revenue and Profits:
RevenueORProfits =
SWITCH( TRUE(),
VALUES( 'Measure Table'[RevORPro] ) = "Total Revenue", [Revenue],
VALUES( 'Measure Table'[RevORPro] ) = "Total Profits", [Profit], BLANK() )

Formula for Items and Customers:
ItemsORCustomers =
SWITCH( TRUE(),
VALUES( 'Item/Customer'[Criteria] ) = "Items", 'Measure Table'[RevenueORProfits],
VALUES( 'Item/Customer'[Criteria] ) = "Customers", 'Measure Table'[RevenueORProfit], BLANK() )


The measure I have to display Revenue or Profits; Items or Customers is:

Measure = IF(
HASONEVALUE( 'Ranking'[RankSelection] ),
IF(
RANKX(
ALL( 'Sales Table'[Custom] ),
[ItemsORCustomers]
)<=VALUES( 'Ranking'[RankSelection] ),
[ItemsORCustomers],
BLANK() ) )

When my "Ranking" is a numeric value, everything works.


Everything below this point does not work.

When I try to change it to a DAX formula for ASC (Bottom 5) or DESC (Top 5), nothing works.

Here's my formula for Rank ASC and DESC:
Rank ASC =
CALCULATE( 'Measure Table'[RevenueORProfits],
TOPN(5, 'Item/Customer', 'Item/Customer'[ItemsORCustomers], ASC) )

And SWITCH statement for Top/Bottom:
TB Selection =
SWITCH( TRUE(),
VALUES( 'TB Table'[TB] ) = "Top 5", [Rank DESC],
VALUES( 'TB Table'[TB] ) = "Bottom 5", [Rank ASC], 0)

Any help or suggestions appreciated. Thank you.

 

[QTHUYNH]

SOLVED - https://community.powerbi.com/t5/De...ure-by-topn-both-in-asc-order-and/td-p/166603