Calculated column or measure using IF statement

Question

Objective:

I would like to make a measure and a calculated column (for the sake of knowing how to write both) using an IF statement but can't get it to work

Query:

Column = 
IF(
    Refund[orderTotalPrice]=Refund[amount] && Refund[status] = 'refund' , 
    Refund[amount] - Refund[total_tax]- Refund[shipping_price],
    Refund[amount]
    )

the expression refers to multiple columns multiple columns cannot be converted to a scalar value

Are you getting that error using it for a calculated column, as a measure, or both? — Alexis Olson

tim tim · Accepted Answer · 2019-07-17T21:59:13

When creating an if statement in a calculated column you can only have one comparison statement. If you want 2, like in your example, you need to use the AND function. Also make sure you use " instead of ' for string comparison.

I tested this calculated column and this worked for me:

Column = if(
AND(Refund[orderPriceTotal]=Refund[amount],Refund[status]="Refund"),
Refund[amount] - Refund[total_tax] - Refund[shipping price],
Refund[amount]
)

In your case, I do not think there is an easy solution to solve this in a measure. Why would you want to build it as a measure?

Calculated column or measure using IF statement

1 Answers