How do I correct this DAX to add a filter?

[Mjdrejza1722]

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?

[nchr]

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.

[FreemanZ]

hi @Mjdrejza1722 

please paste your code as text firstly

[Mjdrejza1722]

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]))

                    )

                )