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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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