Post reply

Case statement error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. "

  • March 22, 2016 at 6:10 pm

    #150485

    switchTest:=SWITCH('POEC Delivery Types', "MCS T&M", SUM([one thing]),'POEC Delivery Types', "Subcon T&M", SUM([another thing]),0)

    I'm trying to say that if the Fact column called POECDeliveryType has value "MCS T&M" then SUM([one thing]), and if the Fact column called POECDeliveryType has value "Subcon T&M", then SUM([another thing]), else use value 0.

    <error I get>

    The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    Can someone see what is the problem?

    --Quote me

  • March 23, 2016 at 4:58 am

    #1867027

    polkadot (3/22/2016)

    switchTest:=SWITCH('POEC Delivery Types', "MCS T&M", SUM([one thing]),'POEC Delivery Types', "Subcon T&M", SUM([another thing]),0)

    I'm trying to say that if the Fact column called POECDeliveryType has value "MCS T&M" then SUM([one thing]), and if the Fact column called POECDeliveryType has value "Subcon T&M", then SUM([another thing]), else use value 0.

    <error I get>

    The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    Can someone see what is the problem?

    Your SWITCH statement is incorrect. As per BOL it follows this syntax: SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

    So your statement should be: switchTest:=SWITCH('POEC Delivery Types', "MCS T&M", SUM([one thing]), "Subcon T&M", SUM([another thing]),0)

    I'm on LinkedIn

  • March 23, 2016 at 6:01 am

    #1867043

    The above identifies one issue. Also, it looks like 'POEC Delivery Types' is a table. This should be a single column.

  • March 23, 2016 at 9:50 am

    #1867141

    Thank you both for helping to explain some finickiness with SWITCH.

    I needed a switch statement that could analyze a whole column for existence of "MCS T&M" and "Subcon T&M" values and then to sum the corresponding values in another column.

    Turns out I needed SUMX() for that and I am using column. Was advised as best practice to always table qualify the column.

    This is what is working for me now:

    =SUMX('FF Fact',SWITCH('FF Fact'[POECDeliveryType], "MCS T&M",'FF Fact'[an adjacent column],"Subcon T&M", 'FF Fact'[another adjacent column],0))

    --Quote me

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply