Formula for multiple "LIKE" statements in DAX?

[RobMorris]

I'm new to PowerBI having moved over from SAP Crystal Reports and one of the issues I'm having is replacing the custom formulas I had created previously.

We have a table that shows the type of streetlights that are used within the Borough and in Crystal I had the following formula set up:

If {LAMPS.LAMPDESC1} like "*LED*" then "LED" else

If {LAMPS.LAMPDESC1} like "*SON*" then "High Pressure Sodium - SON" else
If {LAMPS.LAMPDESC1} like "*High Pressure Sodium*" then "High Pressure Sodium - SON" else

If {LAMPS.LAMPDESC1} like "*SOX*" then "Low Pressure Sodium - SOX" else
If {LAMPS.LAMPDESC1} like "*Low Pressure Sodium*" then "Low Pressure Sodium - SOX" else

If {LAMPS.LAMPDESC1} like "*CPO*" then "Cosmopolis" else
If {LAMPS.LAMPDESC1} like "*Cosmopolis*" then "Cosmopolis" else

If {LAMPS.LAMPDESC1} like "*Electronic Ballast*" then "Electronic Ballast" else

If {LAMPS.LAMPDESC1} like "*High Pressure Mercury*" then "High Pressure Mercury" else

If {LAMPS.LAMPDESC1} like "*Low Pressure Mercury*" then "Low Pressure Mercury" else

If isnull ({LAMPS.LAMPDESC1}) then "No data" else

"Others"

Trying to set this up in PowerBI has got my completely confused.  I have found help on here for if I want to search for 2 types of values using the following formula

Column =

IF (

SEARCH ( "SON*", LAMPS[LAMPDESC1],, 0 ) = 0,

IF ( SEARCH ( "LED*", LAMPS[LAMPDESC1],, 0 ) = 0, "Others", "LED Lights" ),

"SON Lights"

)

but how do I expand that out to include MULTIPLE searchs as per the previous formula I was using?

Any help would be very gratefully received!!!

[Anonymous · Accepted solution]

This should work:

Column =
    SWITCH(TRUE(),
        SEARCH("LED", LAMPS[LAMPDESC1], 1, 0) > 0, "LED",
        SEARCH("SON", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",
        .
        .
        .
        ISBLANK(LAMPS[LAMPDESC1]), "No data",
        "Others"
    )

[RobMorris]

Column =

SWITCH(TRUE(),

SEARCH("LED", LAMPS[LAMPDESC1], 1, 0) > 0, "LED",

SEARCH("SON", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",

SEARCH("*High Pressure Sodium*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",

SEARCH("*SOX*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",

SEARCH("*Low Pressure Sodium*", LAMPS[LAMPDESC1], 1, 0) > 0, "Low Pressure Sodium - SOX",

SEARCH("*CPO*", LAMPS[LAMPDESC1], 1, 0) > 0, "Cosmopolis",

SEARCH("*Cosmopolis*", LAMPS[LAMPDESC1], 1, 0) > 0, "Cosmopolis",

SEARCH("*Electronic Ballast*", LAMPS[LAMPDESC1], 1, 0) > 0, "Electronic Ballast",

SEARCH("*High Pressure Mercury*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Mercury",

SEARCH("*Low Pressure Mercury*", LAMPS[LAMPDESC1], 1, 0) > 0, "Low Pressure Mercury",

ISBLANK(LAMPS[LAMPDESC1]), "No data",

"Others"

)

Brilliant - this has worked perfectly!  Thanks for your help.

[Anonymous]

Thanks a lot