Forum Discussion
Customized filter behavior with button/slicer etc
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!!!
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.
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
