topic How do I correct this DAX to add a filter? in DAX Commands and Tips

How do I correct this DAX to add a filter?

Anonymous — Fri, 10 Feb 2023 15:34:44 GMT

In the measure below, I have reused the DAX expression in blue which is working elsewhere in my model just fine. I want to now add a filter (i.e., [CATEGORY] <> "Dateline"), but don't have the DAX syntax correct because now I am getting this "LOOKUPVALUE' usage error.

I cannot figure out what the correct syntax is. Any help on this?

Re: How do I correct this DAX to add a filter?

FreemanZ — Sat, 11 Feb 2023 00:04:51 GMT

hi Anonymous

please paste your code as text firstly

Re: How do I correct this DAX to add a filter?

nchr — Sat, 11 Feb 2023 17:38:50 GMT

If I am not mistaken (it's a screenshot and has inconvenient formating) the SWITCH statement is the second argument to your CALCULATE.

CALCULATE arguements (after the first) should be true/false tables that are used a s filter context for the calculation. Your SWITCH does not return a true/false table, it returns a number, e.g. 13. So your CALCULATE is equivalent to:
CALCULATE( FILTER (...,...), 13)

This is the error, described in the yellow error message as well.

Re: How do I correct this DAX to add a filter?

Anonymous — Thu, 16 Feb 2023 14:12:10 GMT

Here is the code. What I am trying to accomplish is to have the SWITCH statement calculate for all the categories except the Dateline (which I have to include in the visual-level slicer because that category is used elsewhere in the visual on a secondary axis).

Amount SC Switch 2 =

CALCULATE(

filter('Final Spend', 'Final Spend'[CATEGORY] <> "Dateline"),

SWITCH(TRUE(),

MAXx('Final Spend', [Project Status])="Approved", sumx(FILTER('Final Spend','Final Spend'[Project Status] = "Approved"&&'Final Spend'[CATEGORY]<>"Dateline"),'Final Spend'[Amount USD])*(-1)*LOOKUPVALUE('Currency_Exchange_Rate'[ExchRate],'Currency_Exchange_Rate'[Currency],max('Currency_Exchange_Rate'[Currency])),

MAXx('Final Spend', [Project Status])="Completed", sumx(FILTER('Final Spend','Final Spend'[Project Status] = "Completed"&&'Final Spend'[CATEGORY]<>"Dateline"),'Final Spend'[Amount USD])*(-1)*LOOKUPVALUE('Currency_Exchange_Rate'[ExchRate],'Currency_Exchange_Rate'[Currency],max('Currency_Exchange_Rate'[Currency])),

MAXx('Final Spend', [Project Status])="Intake", sumx(FILTER('Final Spend','Final Spend'[Project Status] = "Intake"&&'Final Spend'[CATEGORY]<>"Dateline"),'Final Spend'[Amount USD])*LOOKUPVALUE('Currency_Exchange_Rate'[ExchRate],'Currency_Exchange_Rate'[Currency],max('Currency_Exchange_Rate'[Currency])),

MAXx('Final Spend', [Project Status])="Prioritized", sumx(FILTER('Final Spend','Final Spend'[Project Status] = "Prioritized"&&'Final Spend'[CATEGORY]<>"Dateline"),'Final Spend'[Amount USD])*LOOKUPVALUE('Currency_Exchange_Rate'[ExchRate],'Currency_Exchange_Rate'[Currency],max('Currency_Exchange_Rate'[Currency]))

)