Forum Discussion
Dynamic Measures based on filter selection
- 9 years ago
Anonymous
1. Create a New Table with
Calc
YTD
Monthly
2. Then Use a Slicer with this column
3. Create a measure
Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)
I just finished a straight hour of writing a series of measures that do exactly this. Here's my method:
Use "Enter Data" to create a new table. Call it something like "MeasureBehavior" and make a column called "Stat". You can call it whatever you want, but that's what I call it. Stat is the name of the method you want to use, so Monthly, YTD, whatever else...
Then your measures will take the following form:
Measure = IF( HASONEVALUE(MeasureBehavior[Stat]), SWITCH( FIRSTNONBLANK(MeasureBehavior[Stat], 1), "Monthly", SUM(TableName[ColumnName]), "YTD", CALCULATE( SUM(TableName[ColumnName]), DATESYTD(DateTable[Date]) ), BLANK() ), BLANK() )
The BLANK() functions are defaults for when conditions are not met. The first one is if there is only one stat selected but it's not either Monthly or YTD. This condition should never actually happen but I think it's best to give SWITCH a failsafe blank as a general rule. The last BLANK() is for cases when more than one stat is selected (or when the slicer has no selection, which is the same thing). You may prefer to use the plain monthly sum here, so the default will always be monthly unless YTD is explicitly selected. Up to you.
Oh, then of course you would add a slicer for MeasureBehavior[Stat] to the report.