Forum Discussion
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.
Thanks that works.
My assumption is you can't compare multiple columns in an if statement?
3 Replies
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"))
Thanks that works.
My assumption is you can't compare multiple columns in an if statement?I think it was more along the lines of having too many "ands" involved without nesting the if statement.
