Forum Discussion

LinhNguyen's avatar
LinhNguyen
Icon for Helper II rankHelper II
6 years ago
Solved

Customized filter behavior with button/slicer etc

Hi,   I'm new to PowerBI and I'm looking for a way create a custom filter.   I want to create some buttons that would help me apply filters that I want into the dashboard (image below). The idea ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    You could do this by creating a disconnected table with single column with 2 values "YES" and "NO"

     

    Then you could create a measure like the following

     

    Total Amount = 
VAR _includeBonus = SELECTEDVALUE('Include Bonus'[Include Bonus])
RETURN IF(_includeBonus = "NO", CALCULATE( SUM('Table'[Amount]), KEEPFILTERS('Table'[Bonus] = "NO")), SUM('Table'[Amount]))

     

     

     

    I've attached a copy of my test file to this post if you are interested.

LinhNguyen's avatar
LinhNguyen
Icon for Helper II rankHelper II
6 years ago

d_gosbell 

Awesome! That's exactly what I was looking for.

 

One more question: I'll need to create a time filter like in the picture (year to date, quarter to date, month to date). What do you think is the best way to do that? I suppose I shouldn't use the same approach because if so, for each column in my table, I'll need to create 6 scenarios (with/without bonus for 3 time periods).

 

I've marked your previous reply as the solution but please let me know if you see any other way to do this.

 

Thank you!!!

 

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago
LinhNguyen wrote:

One more question: I'll need to create a time filter like in the picture (year to date, quarter to date, month to date). What do you think is the best way to do that? I suppose I shouldn't use the same approach because if so, for each column in my table, I'll need to create 6 scenarios (with/without bonus for 3 time periods).

 

You are correct, definitely do not go creating multiple new columns. I'm assuming that you already have a date column in your table. The best practice with date calculations is to have a separate date table in your model with a relationship to the date in your fact table. then you could use a pattern like the following

 

My Amount = 

VAR _selectedPeriod = SELECTEDVALUE( 'Period Selection'[Period Selection] )
RETURN SWITCH( _selectedPeriod,
"QTD", CALCULATE( [Total Amount], DATESQTD( 'Date'[Date] ) 
"YTD", CALCULATE( [Total Amount], DATESYTD( 'Date'[Date] )
... etc
) 

 

So in the example above
[Total Amount] - is the measure from the previous reply which does the bonus inclusion

'Period Selection' - is a disconnected table like the one we created to generate the include bonus Yes/No option
'Date' - is a separate date table
DATESQTD & DATESYTD are built in "time intelligence" functions but you could use any custom filter expression you liked