Forum Discussion

tonijj's avatar
tonijj
Helper IV
5 years ago
Solved

Switch function with LEFT different conditions

Hi,

 

I have a datatable with Order numbers. The number series translate to different types of Orders. I would like to create a DAX formula that does the translation, like SWITCH. 

 

The rules that I have are;

 

(A) G101... = Capex   - all order numbers starting with "G101" are considered to be Capex.

(B) G102... = OPEX   - as above

(C) G104... = Capex- as above

(D) F*** = Capex  - All orders starting with just "F" is to be considered to be Capex

(E) O*** = Opex - All orders starting with just "O" is to be considered to be Capex

 

I have tried SWITCH together with LEFT. However, as A-C have a condition looking at the first 3 characters, and D-E only looks at the first character...Im stuck 😕 

 


SWITCH(
LEFT(OrderNumber[OrderNumber]; 3); "G101"; "CAPEX";
LEFT(OrderNumber[OrderNumber]; 3); "G104"; "CAPEX";
LEFT(OrderNumber[OrderNumber]; 3); "G102"; "OPEX";
LEFT(OrderNumber[OrderNumber]; 1); "F"; "CAPEX";
LEFT(OrderNumber[OrderNumber]; 1); "O"; "OPEX";
)
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    tonijj,

     

    Try this calculated column:

     

    Category = 
SWITCH (
    TRUE (),
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G101", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G104", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G102", "OPEX",
    LEFT ( OrderNumber[OrderNumber], 1 ) = "F", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 1 ) = "O", "OPEX"
)

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    tonijj,

     

    Try this calculated column:

     

    Category = 
SWITCH (
    TRUE (),
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G101", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G104", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 4 ) = "G102", "OPEX",
    LEFT ( OrderNumber[OrderNumber], 1 ) = "F", "CAPEX",
    LEFT ( OrderNumber[OrderNumber], 1 ) = "O", "OPEX"
)