Forum Discussion
rpinxt
Solution Sage
Solution SageFiltering based on a variable threshold
I have this Dax code " >100k = var startDate = EOMONTH( TODAY(), -2) + 1 var endDate = EOMONTH(TODAY(), -1) var valueLastMonth = CALCULATE( SELECTEDVALUE(prmMeasure[Selected Measure]), REMOVE...
Well maybe it is not the best solution when field parameters are involved but I went with this solution.
Using a switch to get the selected measure. There are only 3 so that is ok.
>Threshold =VAR startDate = EOMONTH(TODAY(), -2) + 1VAR endDate = EOMONTH(TODAY(), -1)VAR selectedMeasure = SELECTEDVALUE(prmMeasure[Selected Measure])VAR valueLastMonth =CALCULATE(SWITCH(TRUE(),selectedMeasure = "Tot.GC", [Tot.GC],selectedMeasure = "Tot.LC", [Tot.LC],selectedMeasure = "Quantity", [Quantity],BLANK()),REMOVEFILTERS(dimDate),DATESBETWEEN(dimDate[Date], startDate, endDate))RETURNIF( valueLastMonth > Threshold[Threshold Value], 1, 0)
rpinxtSolution Sage
Solution SageWell maybe it is not the best solution when field parameters are involved but I went with this solution.
Using a switch to get the selected measure. There are only 3 so that is ok.
>Threshold =
VAR startDate = EOMONTH(TODAY(), -2) + 1
VAR endDate = EOMONTH(TODAY(), -1)
VAR selectedMeasure = SELECTEDVALUE(prmMeasure[Selected Measure])
VAR valueLastMonth =
CALCULATE(
SWITCH(
TRUE(),
selectedMeasure = "Tot.GC", [Tot.GC],
selectedMeasure = "Tot.LC", [Tot.LC],
selectedMeasure = "Quantity", [Quantity],
BLANK()
),
REMOVEFILTERS(dimDate),
DATESBETWEEN(dimDate[Date], startDate, endDate)
)
RETURN
IF( valueLastMonth > Threshold[Threshold Value], 1, 0)