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:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Basically it is transactions and I want to personalize the positive or negative sign for reporting purposes.

 

I would need that the new column: REPORTING AMOUNT multiplies the AMOUNT values by -1 or +1 depending on the first number of the column Account Number. For instance, If the Account Number starts with 1 or 4 then the Amount value should be multiplied by -1 in the case that the Account Number starts with 2,3, or 5, then the AMOUNT should be multiplied by +1.

 

 

Thanks in advance for the help!

 

Luis

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

4 Replies

  • 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