Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
cristianml
Post Prodigy
Post Prodigy

IN FUNCION IN CALCULATED COLUMN

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)
 

cristianml_0-1646250656737.png

 

Thanks!

1 ACCEPTED SOLUTION
ElenaN
Resolver V
Resolver V

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

View solution in original post

3 REPLIES 3
ElenaN
Resolver V
Resolver V

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
Resolver V
Resolver V

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

Hi @ElenaN ,

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors