Forum Discussion

blazer12219's avatar
blazer12219
Frequent Visitor
6 years ago

DAX IF Statement formula in added Column

Accounting SQL database accessing does recognize Credit Memos as negatives so when add total Taxable Sales the total is wrong.

 

Attempting to add new Column to Database that looks at TrxType 2 which is a credit memo than multiples that total by -1.0 to turn it to a negative that if anything else it just returns the TaxableAmt for that row so when added all together the total is correct.

 

When attempting formula just will not calculate properly

 

IF [TrxType] = 2 then ([TaxableAmt]*-1) else ([TaxableAmt])) also tried

IF([TrxType]=2,([TaxableAmt]*-1),([TaxableAmt]))

 

any help appreciated,

Need Help

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    You are better off doing this in Power Query. I suspect your TrxType isn't an integer is why DAX is failing, but you can clearly see the data type in Power Query. Assuming it is an integer, you'd use this:

     

     

    if [TrxType] = 2 then -[TaxableAmt] else [TaxableAmt]

     

     

    If it is a string, then use this

     

     

    if [TrxType] = "2" then -[TaxableAmt] else [TaxableAmt]

     

     

    Then get rid of the original TaxableAmt column, and rename the new custom column TaxableAmt, make sure it is typed as a number, integer, or currency (number with 4 decimal places) to suit your needs, then close and load.