Calculating a revised plantID with SWITCH

[shadowsong42]

I have a fact table with (among other things) countryCode, countrySubDivisionCode, and plantID - all text columns. For everything other than US and Canada, I want to retain the original plantID. For US and Canada, I want to override the plantID with new values based on the countrySubDivisionCode.

When I try to create this as a calculated measure, it doesn't recognize the measure names, so I tried a calculated column instead. It didn't pop up any errors when I created the column with the below code, but when I tried to reference it in a visual, I got an error saying "a table of multiple values was supplied where a single value was expected".

Calculated Plant = 
VAR List4260 = { "NB", "NL", "NS", "ON", "PE", "QC" }
VAR List4790 = { "AB", "BC", "MB", "NT", "NU", "SK", "YT" }

RETURN
IF( OrderSlaLineItemDetail[countryCode] IN { "CA","US" },
    SWITCH(
        OrderSlaLineItemDetail[countrySubDivisionCode],
        List4260, "4260",
        List4790, "4790",
        OrderSlaLineItemDetail[plantId]
    ),
    OrderSlaLineItemDetail[plantId]
)

How do I need to change this to end up with a calculated plant assigned to each row of the fact table, in a way that will let me use it in a visual?

[v-pgoloju]

Hi @shadowsong42 

Thank you for reaching out to the Microsoft Fabric Forum Community.

And also thanks to @vicky_  for prompt and useful response.

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[vicky_ · Accepted solution]

Hi, 

the issue pops up because of the parts List4260, List4790 in the switch statement. Note - both of them are lists, when the SWITCH statement is trying to compare [countrySubDivisionCode] (which is a single string) against your list table of values. 

To fix this, you can try make the following change:

Calculated Plant = 
VAR List4260 = { "NB", "NL", "NS", "ON", "PE", "QC" }
VAR List4790 = { "AB", "BC", "MB", "NT", "NU", "SK", "YT" }

RETURN
IF( OrderSlaLineItemDetail[countryCode] IN { "CA","US" },
    SWITCH(TRUE(), 
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4260, "4260",
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4790, "4790",
        OrderSlaLineItemDetail[plantId]
    ),
    OrderSlaLineItemDetail[plantId]
)