cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Max_mutant Frequent Visitor
Frequent Visitor

DAX Syntax for If Statement with Filter

Hello Everyone,

 

Here's what I am trying to do.

 

Right now I have a measure thats pulling back import duties based on a country.

 

msr_221ImportDutyRate = If (ISFILTERED('Trade Compliance'[Grower Country (Exporter)]) && HASONEVALUE('Trade Compliance'[Grower Country (Exporter)]), CONCATENATEX(VALUES('Trade Compliance'[Item Duty Rate]),[Item Duty Rate],", ",[Item Duty Rate],ASC),"")

 

What I want to do is to further filter these results based on an agricultural product; e.g. lettuce, onions, tomatoes.


I add in the following filter function clause in Red below.

 

msr_221ImportDutyRateWithProductFilter = If (((ISFILTERED('Trade Compliance'[Destination Country (Importer)]) && HASONEVALUE('Trade Compliance'[Destination Country (Importer)])) &(FILTER('TradeCompliance','TradeCompliance'[Product]="Lettuce"))) , CONCATENATEX(VALUES('Trade Compliance'[Item Duty Rate]),[Item Duty Rate],", ", [Item Duty Rate],ASC),"")

 

What I don't understand is why this gives me the error message of 'The expression refers to multiple columns. Multiple Columns cannot be converted to scalar values.

 

Sorry if this is a basic question, I am new to DAX.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Max_mutant Frequent Visitor
Frequent Visitor

Re: DAX Syntax for If Statement with Filter

Thanks that works.


My assumption is you can't compare multiple columns in an if statement?

View solution in original post

3 REPLIES 3
kcantor Super Contributor
Super Contributor

Re: DAX Syntax for If Statement with Filter

@Max_mutant

Your measure does refer to two columns. Exporter and Product. Have you considered using your measure inside a calculate and using your filter there?

msr_221ImportDutyRate = If (ISFILTERED('Trade Compliance'[Grower Country (Exporter)]) && HASONEVALUE('Trade Compliance'[Grower Country (Exporter)]), CONCATENATEX(VALUES('Trade Compliance'[Item Duty Rate]),[Item Duty Rate],", ",[Item Duty Rate],ASC),"")

msr_221ImportDutyRateWithProductFilter =CALCULATE([msr_221ImportDutyRate], FILTER('TradeCompliance','TradeCompliance'[Product]="Lettuce"))



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Max_mutant Frequent Visitor
Frequent Visitor

Re: DAX Syntax for If Statement with Filter

Thanks that works.


My assumption is you can't compare multiple columns in an if statement?

View solution in original post

kcantor Super Contributor
Super Contributor

Re: DAX Syntax for If Statement with Filter

I think it was more along the lines of having too many "ands" involved without nesting the if statement.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 305 members 3,014 guests
Please welcome our newest community members: