Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How do I correct this DAX to add a filter?

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?

 



 

 

Filter
LOOKUPVALUE
switch

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    please paste your code as text firstly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

                          )

                      )

  • nchr's avatar
    nchr
    Frequent Visitor
    3 years ago

    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.