Calculation needed based on multiple IF statements

[EllyBrouwers]

I need to define the number of HW units signed per an accoutn in a dataset where there are multiple line items per account, to ensure that I only have the correct value it needs to comply to various statements. I currently tried the below but that doesn't work I'm getting the error [Expressions that yield variant data-type cannot be used to define calculated columns]

HW signed = if('SDL Deal details Report NWE'[Category]="Hardware",

if('SDL Deal details Report NWE'[ComponentClass]="printer",

if('SDL Deal details Report NWE'[NetRevenue]>0,

'SDL Deal details Report NWE'[Quantity],"n.a.")))

[Anonymous]

Hi @EllyBrouwers ,

With multiple conditions, you can try && and || (DAX), or AND and OR (M language).

For example,

if('SDL Deal details Report NWE'[Category]="Hardware" && 

SDL Deal details Report NWE'[ComponentClass]="printer"&& 

'SDL Deal details Report NWE'[NetRevenue]>0,

'SDL Deal details Report NWE'[Quantity],"n.a.")))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

The column type can't have mixed data type. 

'SDL Deal details Report NWE'[Quantity] - This gives number when TRUE.

"n.a." - This gives text when TRUE.

A work around can be that replace n.a. with 0 so that you get only numeric column.

Another workaround is that 'SDL Deal details Report NWE'[Quantity] gives Text output so that your column is treated as text.

For first case

HW signed = if('SDL Deal details Report NWE'[Category]="Hardware",
if('SDL Deal details Report NWE'[ComponentClass]="printer",
if('SDL Deal details Report NWE'[NetRevenue]>0,
'SDL Deal details Report NWE'[Quantity],0)))

For second case when you want to treat column as text, use FORMAT
= if('SDL Deal details Report NWE'[Category]="Hardware",

if('SDL Deal details Report NWE'[ComponentClass]="printer",

if('SDL Deal details Report NWE'[NetRevenue]>0,

FORMAT('SDL Deal details Report NWE'[Quantity],"General number"),"n.a.")))