Getting scalar error using Switch function

[Anonymous]

Very new to DAX. Trying to use Switch for the first time. I am getting the error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.' I am not having any luck coming up with a solution. Here is the code:

IVC - Delivered Cubic Realization:=SWITCH(
     SEARCH("PL",FILTER('Invoice Fact', RELATED('Product Dimension'[Product Family Code]))),
              DIVIDE('Invoice Fact'[IVC - Delivered Amount Extended], 'Invoice Fact'[IVC - Cubic Conversion Footage]) * 1000
    ,SEARCH("VE",FILTER('Invoice Fact', RELATED('Product Dimension'[Product Family Code]))),
              DIVIDE('Invoice Fact'[IVC - Delivered Amount Extended], 'Invoice Fact'[IVC - Cubic Conversion Footage]) * 1000
    ,SEARCH("EWP",FILTER('Invoice Fact', RELATED('Product Dimension'[Product Family Code])),"BEAM",FILTER('Invoice Fact', RELATED('Product Dimension'[Product Category Code]))),
              DIVIDE('Invoice Fact'[IVC - Delivered Amount Extended], 'Invoice Fact'[IVC - Cubic Conversion Footage]) * 1000
    ,DIVIDE('Invoice Fact'[IVC - Delivered Amount Extended], 'Invoice Fact'[IVC - Cubic Conversion Footage])
)

[Regex]

Hi,

The code is very confusing and seems to have multiple issues.

However I can immediately see that you are getting that error because DIVIDE refers to columns directly without an aggregator over both the numerator and denominator (probably you'd want to use SUM). It won't be the only error even if you fix it.

I suggest first adding a helper column to Invoice Fact that contains a string (e.g. "EWP"), which will define what logic is used in the SWITCH table. You need to get that right first.

Good luck!

[dedelman_clng]

Hi @Anonymous  - Your use of SWITCH appears to be OK. It is the other functions that are an issue

SEARCH requires a scalar text value as its second parameter - FILTER returns a table.

SEARCH also generally needs a 4th parameter, which is what to do if SEARCH doesn't find what you're looking for (it returns ERROR if you omit the 4th parameter).  Your usage will be something like SEARCH("A", SearchInString, , 0)

Also, SEARCH returns an integer, not TRUE/FALSE, so you will need to compare the result of SEARCH to something

(SWITCH( SEARCH (...) > 0, ....)

This is a good resource for DAX functions: https://dax.guide/ 

Hope this helps

David