Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
1 year ago
Solved

Filtering 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...
Need Help
  • rpinxt's avatar
    rpinxt
    1 year ago

    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) + 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)
rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
1 year ago

Was a bit quick....so this is going wrong

var valueLastMonth = CALCULATE( SELECTEDVALUE(prmMeasure[Selected Measure]), REMOVEFILTERS(dimDate)

 

This line returns the name of the measure and not the outcome of the measure like column Tot.GC.

 

These are field parameters in a slicer so user can choose what measure to use. (USD or EUR etc).

 

Is there a simple way to ammend the code?