Power BI Dynamic Filtering

QTHUYNH

New Member
Joined
Aug 31, 2017
Messages
7
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.
 
Last edited:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,334
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top