Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Two Filter Conditions & Dax Error

Hi Everyone,

I need to filter data on 2 conditions. Can anyone help me out?

error: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Accessory Tiers = 
Var _tier1 = CALCULATE(SUM(TierGoals[TIER 0 - MAX]),FILTER(TierGoals,TierGoals[JOB FUNCTION]="RSM") && FILTER(TierGoals,TierGoals[ITEM]="Accessory Goal"))
Var _tier2 = CALCULATE(SUM(TierGoals[TIER 1 - MAX]),FILTER(TierGoals,TierGoals[JOB FUNCTION]="RSM") && FILTER(TierGoals,TierGoals[ITEM]="Accessory Goal"))
Var _tier3 = CALCULATE(SUM(TierGoals[TIER 2 - MAX]),FILTER(TierGoals,TierGoals[JOB FUNCTION]="RSM") && FILTER(TierGoals,TierGoals[ITEM]="Accessory Goal"))
Var _tier4 = CALCULATE(SUM(TierGoals[TIER 3 - MAX]),FILTER(TierGoals,TierGoals[JOB FUNCTION]="RSM") && FILTER(TierGoals,TierGoals[ITEM]="Accessory Goal"))
Var _tier5 = CALCULATE(SUM(TierGoals[TIER 4 - MAX]),FILTER(TierGoals,TierGoals[JOB FUNCTION]="RSM") && FILTER(TierGoals,TierGoals[ITEM]="Accessory Goal"))
Var _ActualGoal = [Accessory % TO GOAL]
Return
IF(_ActualGoal>=_tier1,_tier1,IF(_ActualGoal>=_tier2,_tier2,IF(_ActualGoal>=_tier3,_tier3,IF(_ActualGoal>=_tier4,_tier4,0))))

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Try this syntax for your two conditions. You don't need two FILTER statements:

        CALCULATE(
        SUM( TierGoals[TIER 0 - MAX] ),
        FILTER(
            TierGoals,
            TierGoals[JOB FUNCTION] = "RSM"
                 && TierGoals[ITEM] = "Accessory Goal"
        )
     )

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Try this syntax for your two conditions. You don't need two FILTER statements:

        CALCULATE(
        SUM( TierGoals[TIER 0 - MAX] ),
        FILTER(
            TierGoals,
            TierGoals[JOB FUNCTION] = "RSM"
                 && TierGoals[ITEM] = "Accessory Goal"
        )
     )