cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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?

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?

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 877 guests
Please welcome our newest community members: