Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
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.
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
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.
@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.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |