topic Re: Calculated Column in DAX Commands and Tips

Calculated Column

Anonymous — Wed, 24 Jun 2020 15:32:33 GMT

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.

Re: Calculated Column

AntrikshSharma — Wed, 24 Jun 2020 15:40:17 GMT

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

Re: Calculated Column

Anonymous — Thu, 25 Jun 2020 06:58:43 GMT

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,

)

RETURN

Result

But still returns an error message. Please what do i define inside of the RETURN function

Re: Calculated Column

Anonymous — Thu, 25 Jun 2020 07:10:36 GMT

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)