Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LinhNguyen
Helper II
Helper II

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 is to include all records if viewer choose "Yes" in "include bonus" and exclude only the bonus records if they choose "No". I also need the filter to apply to all of my other reports and not just the current one.

 

If I create a calculated column that has "no" in records that are not bonus and "yes" in records that are bonus, I can create a slicer but that slicer is going to have 3 options (include all/exclude one or the other), however I only want to show users these 2 options of including all or excluding the bonus.

 

I also tried looking at buttons and bookmarks to change between views, however the bookmark will lead viewer to a separate view and thus all other selections outside of the bookmark button will not be kept.

 

Is there any way I can do this in PowerBI?

 

cust filter.PNG

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User



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

 

IncludeExclude.gif

 

 

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

View solution in original post

5 REPLIES 5
d_gosbell
Super User
Super User



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

 

IncludeExclude.gif

 

 

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

@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!!!

 


@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

Anonymous
Not applicable

One question similar to this.

What if 'Include Bonus' Slicer has 10 different values and enabled for selecting multiple values.

in this case how do I check if only 'Yes' is selected.

 

MultiSelect.PNG


@Anonymous wrote:

One question similar to this.

What if 'Include Bonus' Slicer has 10 different values and enabled for selecting multiple values.

in this case how do I check if only 'Yes' is selected.

 

The SELECTEDVALUE() function only returns a value if there is a single selection. If there is no selection or multiple selections it will return blank by default or you can add an optional second parameter to indicate the value you want returned in the case where there is no a single selection.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.