SWITCH and LEFT with multiple conditions

[tonijj]

Hi,

I have a set of table with Internal Order numbers, where the number series have different meanings.

(A) G101….. = CAPEX.   All numbers starting with “G101” are considered to be CAPEX

(B) G102… .= OPEX.  All numbers starting with “G102” are considered to be CAPEX

(C) F…. = CAPEX  -   All numbers starting with “F” are considered to be CAPEX

(D) O = OPEX -   All numbers starting with “F” are considered to be OPEX

As A and B have a condition of the first 3 characters and C and D have a condition of only 1 character, I get stuck ☹

Here’s what I tried so far, needles to say, that didn’t work.

SWITCH(

    LEFT(OrderNumber[OrderNumber]; 3); "G101"; "CAPEX";

    LEFT(OrderNumber[OrderNumber]; 3); "G102"; "OPEX";

    LEFT(OrderNumber[OrderNumber]; 1); "O"; "OPEX";

    LEFT(OrderNumber[OrderNumber]; 1); "F"; "CAPEX";

)

[AlB · Accepted solution]

Hi @tonijj 

Check out  Switch syntax:  https://dax.guide/switch/

SWITCH (
    TRUE ();
    LEFT ( OrderNumber[OrderNumber]; 3 ) = "G101"; "CAPEX";
    LEFT ( OrderNumber[OrderNumber]; 3 ) = "G102"; "OPEX";
    LEFT ( OrderNumber[OrderNumber]; 1 ) = "O"; "OPEX";
    LEFT ( OrderNumber[OrderNumber]; 1 ) = "F"; "CAPEX"
)

or alternatively:

VAR _3chars = LEFT ( OrderNumber[OrderNumber]; 3 )
VAR _1char = LEFT ( OrderNumber[OrderNumber]; 1 )
RETURN
    COALESCE (
        SWITCH ( _3chars; "G101"; "CAPEX"; "G102"; "OPEX" );
        SWITCH ( _1chars; "F"; "CAPEX"; "O"; "OPEX" )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[tonijj]

Ah, I did try adding TRUE as well, but not correctly for some reason. Your solution was spot on, thanks a bunch!