Forum Discussion

rpinxt's avatar
rpinxt
Solution 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]), REMOVEFILTERS(dimDate), DATESBETWEEN( dimDate[Date], startDate, endDate) )
return IF( valueLastMonth > Threshold[Threshold Value], 1, 0)
 
What it is supposed to do is this :

The lines are all product families.

So if a product family in the latestmonth (max month whatever) is above a threshold value (100k) in this case it should show that line.

Therefore the If with 0 or 1 in the dax above.

I then filter on 1.

 

But the dax is doing :

 

The threshold value is ok. The selected measure is Yot.GC and is giving values.

So why would it show a 1 and not 0 what I would expect?

 

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)

2 Replies

  • rpinxt's avatar
    rpinxt
    Solution 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?

  • rpinxt's avatar
    rpinxt
    Solution Sage
    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)