DAX Syntax for If Statement with Filter

[Max_mutant]

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.

[kcantor]

@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"))

[Max_mutant · Accepted solution]

Thanks that works.

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

[kcantor]

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