IN FUNCION IN CALCULATED COLUMN

[cristianml]

Hi,

What is the most efficient way to do the following calculated column ?

If Table[Column] contains text1, text2, text3, etc then Result1 else

If Table[Column] contains text4, text5, text6, etc then Result2 

Can IN function be use to calculated columns ?

I tried the following but not sure if works or I should use other function ?

The criteria should be if column contains "SC", "CON","SI" then CON else 

if column contains "AO", "BPO","IO" then OUT else

"OTHERS" 

CON or OUT =

VAR V1 = CONTAINSSTRING('_List SG'[SG] IN { "SC", "CON","SI" },"CON")

VAR V2 = CONTAINSSTRING('_List SG'[SG] IN { "AO", "BPO","IO" },"OUT")

RETURN

IF('_List SG'[SG]=V1,V1,V2)

Thanks!

[ElenaN · Accepted solution]

Hello,

CON or OUT =
IF (
'_List SG'[SG] IN { "SC", "CON", "SI" },
"CON",
IF ( '_List SG'[SG] IN { "AO", "BPO", "IO" }, "OUT", "OTHERS" )
)

Best regards,

ElenaN

[ElenaN]

Hello,

You could try something like:

CON or OUT = 

IF (
    CONTAINSSTRING ( '_List SG'[SG], "SC" )
        || CONTAINSSTRING ( '_List SG'[SG], "CON" )
        || CONTAINSSTRING ( '_List SG'[SG], "SI" ),
    "CON",
    IF (
        CONTAINSSTRING ( '_List SG'[SG], "AO" )
            || CONTAINSSTRING ( '_List SG'[SG], "BPO" )
            || CONTAINSSTRING ( '_List SG'[SG], "IO" ),
        "OUT",
        "OTHERS"
    )
)

However, is it needed to use containsstring as the field SG in the print screen contains exactly the values listed? If it is not needed then you could use directly an IN list of values.

Best regards,

ElenaN

[cristianml]

Hi @ElenaN ,

Could you share the measure with IN funcion instead of containsstring?