Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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?

Mjdrejza1722_1-1676043055277.png

 



 

 

3 REPLIES 3
nchr
Frequent Visitor

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
Super User
Super User

hi @Anonymous 

please paste your code as text firstly

Anonymous
Not applicable

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

                    )

                )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.