Forum Discussion

tonijj's avatar
tonijj
Icon for Helper IV rankHelper IV
5 years ago
Solved

SWITCH and LEFT with multiple conditions

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";

)

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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.

     

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      tonijj
      Icon for Helper IV rankHelper IV
      5 years ago

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