Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Column

Hi All, 

 

I have a dataset with a Transactions Table, I want to create a caculated column that checks for a TransactionType using the TansactionType ID Column and then returns a value based off a calculation on the Amount Column.

 

For example:

If TransactionType is 1, Return amount in Amount Column multiplied by 2

If TransactionType is 2, Return amount in Amount Column.

If TransactionType is 15, Return amount in amount table minus 500.

 

 

So bascially, I want a Calculated Column thats checks for a transactionID, and returns a calulation on the Amount Column.

 

Any help would be appreciated.

DAX Calculation
Dax commands help
Need Help

3 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Try something like this.

     

    Transaction Amount =
VAR TransactionID = Transaction[TansactionType ID]
VAR TransactionAmount = Transaction[Amount]
VAR ReturnAmount =
    SWITCH (
        TRUE (),
        TransactionID = 1, TransactionAmount * 2,
        TransactionID = 2, TransactionAmount,
        TransactionID = 15, TransactionAmount - 500,
        0
    )
RETURN
    Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried this syntax:

       

      Revenue =
      VAR TransactionID = Transactions[TransactionTypeId]
      VAR TransactionAmount = Transactions[Amount($)]
      VAR ReturnAmount =
      SWITCH(
      TRUE(),
      TransactionID = 1, TransactionAmount,
      TransactionID = 2, 6 * 360.5,
      TransactionID = 72, TransactionAmount,
      TransactionID = 4, 0,
      TransactionID = 5, TransactionAmount,
      TransactionID = 12, 0,
      TransactionID = 49, TransactionAmount * 0.65,
      TransactionID = 66, TransactionAmount,
      TransactionID = 68, TransactionAmount,
      TransactionID = 69, TransactionAmount,
      TransactionID = 67, TransactionAmount,
      TransactionID = 35, TransactionAmount - 500,
      0
      )
      RETURN
      Result
       
       
      But still returns an error message. Please what do i define inside of the RETURN function
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI Anonymous ,

         

        You code is okay.

        You need to return Return Amount

         

        Better Formatted Code

         

        Revenue =
VAR TransactionID = Transactions[TransactionTypeId]
VAR TransactionAmount = Transactions[Amount($)]
VAR ReturnAmount =
    SWITCH (
        TRUE (),
        TransactionID = 1
            || TransactionID = 5
            || TransactionID = 72
            || TransactionID = 66
            || TransactionID = 68
            || TransactionID = 69
            || TransactionID = 67, TransactionAmount,
        TransactionID = 2, 6 * 360.5,
        TransactionID = 49, TransactionAmount * 0.65,
        TransactionID = 35, TransactionAmount - 500,
        0
    )
RETURN
    ReturnAmount

         

         

        Regards,

        Harsh Nathani

        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)