How to create an IF statement in Power BI for reporting

[heathernicole]

 Trying to create an 'IF' statement in DAX.

I'm trying to create a calculated column - if Age of Customer is <= 365 - then display 4000

if it's greater than 365 - display nothing - or 0. 

This is what I've tried to use - New Customer Pts = IF(Customer[Age of Customer (in Days)] <= 365, 4000, 0)

But it gives me this error: A single value for column 'Age of Customer (in days)' in table 'Customer' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

It worked for another calculated column, so I'm not sure why it won't for this one. The concept seems the same to me...

Any help is greatly appreciated! 

Thanks! 🙂 

[JeffDuzak · Accepted solution]

Could you double check that you are adding a calc column to the Customer table?  If you are adding a calc column to a different table, then you won't be able to directly refer to a column on the Customer table.

[kcantor]

Have you double checked to make sure you have selected to create a calculated column instead of a measure? It may be something simple as they do live close to each other on the ribbon. Honestly, I did that just today so I figured I would suggest the most simplistic solution first.

[heathernicole]

I actually tried both ways and got the same error message, oddly enough. 

[JeffDuzak · Accepted solution]

Could you double check that you are adding a calc column to the Customer table?  If you are adding a calc column to a different table, then you won't be able to directly refer to a column on the Customer table.

[arpihm]

 HI , can someone please help me here ... Am writing the following but get syntax DAX error.

Recovered Value = if('Recoveries-Month wise breakup'[Status]= "Recovered" or "Posted" ,'Recoveries-Month wise breakup'[Amount],0)

Basically I want the recovered value when the status is recovered or posted .

PLease help .

[heathernicole]

@arpihm - Hello! 🙂 It may be the 'or' part of the statement.

I don't think DAX likes that syntax. I'm still learning - but I have multiple custom fields that have more than criteria for the IF statements.

I'm pretty sure that's not the appropriate syntax. 

Recovered Value = if('Recoveries-Month wise breakup'[Status]= "Recovered" or "Posted" ,'Recoveries-Month wise breakup'[Amount],0)

For multiple criteria IF statements - I usually layer it, so to speak. See below. There may be a better way to do it, but this should work, depending on what the error is.

Recovered Value =
IF (
    'Recoveries-Month wise breakup'[Status] = "Recovered",
    'Recoveries-Month wise breakup'[Amount],
    IF (
        'Recoveries-Month wise breakup'[Status] = "Posted",
        'Recoveries-Month wise breakup'[Amount],
        0
    )
)

[heathernicole]

Ah... that might be it... let me check. I didn't even think of that (or know to think of that, actually). 

[JeffDuzak · Accepted solution]

Could you describe your model a bit more?  Where do you want to define this calculated column?

If you want to define the column in a table that has a many-to-one or one-to-one relationship or path of such relationships to the Customer table, then you *can* refer to columns in the Customer table, but you just have to wrap the reference in a call to the RELATED function.  So, it would look like:

New Customer Pts = IF(RELATED(Customer[Age of Customer (in Days)]) <= 365, 4000, 0)

[heathernicole]

@JeffDuzak - this worked  - for this field it worked perfectly and it's probably more appropriate to relate it to the customer table in this case. 🙂 

Thanks so much! I'm trying to learn DAX as I go - but reports are needed faster than I can learn the syntax. 🙂