HELP nested IFS WITH OR

[Anonymous]

How to do this in DAX?

IF CONTAINS([IDNUMBER],'DMM') OR CONTAINS([IDNUMBER],'NSK')
THEN 'Middle East and Africa'
ELSEIF CONTAINS([IDNUMBER],'APA')
THEN 'Asia Pacific'
ELSEIF CONTAINS([IDNUMBER],'EU')
THEN 'Europe'
ELSEIF CONTAINS([IDNUMBER],'LA')
THEN 'Latin America'
ELSEIF CONTAINS([IDNUMBER],'MEA')
THEN 'Middle East and Africa'
ELSEIF CONTAINS([IDNUMBER],'NA')
THEN 'North America'
END

[Anonymous]

Hi @Anonymous,

If you are handling some specific replacement with column values, you can write a formula based on the switch function to check the different cases of values shared above.
If you need to handle a large number of replacements, I'd like to suggest you create a mapping table with mapping values and replace values, then you can use a simple formula to lookup the mapping table and return results.

Regards,

Xiaoxin Sheng

[CNENFRNL]

Why not use PQ?

let
    Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvH1VdJR8s1MSclJVXBNLC5RSMxLUXBMK8pMTlSK1YlW8gv2xq/AMcARqMCxODNRISAxOTMtMxks7BoKFHUtLcovSAXzfUCqfBJLMvMUHHNT4bp9XR0J2A+S98svKslA6IsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Region = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcxBCoAgEEDRu7huQDNwbRZRqYhTCIn3v0akMLl7i8/PmRkrIeDIypDZ4hzsxpBDeshCqWqPJzVeg+ATUbYLbgmk4p1F576R1W7VwEVrrKb5l8zWk1eH5Hj/xhCbLwshHqyUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ORDER_NUMBER = _t]),

    Matchup = let code = Lookup[Code], region = Lookup[Region] in Table.AddColumn(Source, "Region", each try region{List.PositionOf(code, Text.BeforeDelimiter([ORDER_NUMBER], "-"))} otherwise "XXXXXX")
in
    Matchup

[Fowmy]

@Anonymous 

SWITCH (
    TRUE (),
    [IDNUMBER] IN {"DMM", "NSK"}, "Middle East and Africa",
    
    include each line 
)

[Anonymous]

It's not working. It's just showing blank sir.

@Fowmy 

[Fowmy]

@Anonymous 

Can you show me some order numbers

SWITCH (
    TRUE (),
     NOT(ISEMPTY(FILTER( {"DMM", "NSK"}, CONTAINSSTRING([Value],[IDNUMBER])))), "Middle East and Africa"
    include each line 
)

?

You can try this approach if you are searching within the text:

[Anonymous]

ORDER_NUMBER

CL3-PS2

DMM-ICC

DMM-PWZ

DMM-177

NSK-ICC

NA-104

NA-132

SGW-370

SGW-371

SGW-372

SGW-373

MEA-011

LA-ICC

SGW-BLN

SGW-EMS

SGW-RUS

SGW-SPR

STL-PRJ

@Fowmy 

[Anonymous]

here is the example ^^ @Fowmy 

[Fowmy · Accepted solution]

@Anonymous 

This sort of job is ideally done in Power Query as suggested by @CNENFRNL but if you need a DAX solution and then create a table with all the codes and regions as follows

Then add the Calculated Column:

Order Region = 
var __code = LEFT([ORDER_NUMBER],SEARCH("-",[ORDER_NUMBER])-1 ) 
return
    LOOKUPVALUE( Regions[Region] , Regions[Code] , __code)