Forum Discussion

lhern_ndez's avatar
lhern_ndez
Helper I
8 years ago
Solved

Calculated column with LEFT function and multiple IF depending in other Column conditions

Hello,   I have several columns in a table and I need to create a calculated column which value will depend on the first number of one of the column's value, see extract of the table:     ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi lhern_ndez

     

    Try this, this will work.

     

    New_Col = SWITCH(TRUE(),
                 LEFT(Table1[AcctNo],1) in {"0","1","2","3"},Table1[Amt]*-1,
                 LEFT(Table1[AcctNo],1) in {"4","5","6"},Table1[AcctNo]*1
                     )

     

     

    Thanks
    Raj

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi lhern_ndez

 

Try this, this will work.

 

New_Col = SWITCH(TRUE(),
                 LEFT(Table1[AcctNo],1) in {"0","1","2","3"},Table1[Amt]*-1,
                 LEFT(Table1[AcctNo],1) in {"4","5","6"},Table1[AcctNo]*1
                 )

 

 

Thanks
Raj

  • lhern_ndez's avatar
    lhern_ndez
    Helper I
    8 years ago

    Thanks Rajendran,

     

    It does what needed!

     

    Could you please briefly explain the formula?

    I do not understand the interaction of Switch with True and then the relation with IN....

     

    Thanks!

     

    Luis

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

    SWITCH is also internally performing the IF function only. The above SWITCH is equivalent to the below nested IF statement.

     

    New_Col=

    IF ( LEFT(Table1[AcctNo],1) in {"0","1","2","3"},Table1[Amt]*-1,IF(LEFT(Table1[AcctNo],1) in {"4","5","6"},Table1[AcctNo]*1))

     

    More on SWITCH :

    https://msdn.microsoft.com/en-us/query-bi/dax/switch-function-dax

     

    Hope this helps you.

     

    Thanks

    Raj

    • lhern_ndez's avatar
      lhern_ndez
      Helper I
      8 years ago

      Great, thanks!