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

[lhern_ndez]

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

[Anonymous · Accepted solution]

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]

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]

Great, thanks!

[lhern_ndez]

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