Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

How to create an IF statement in Power BI for reporting

 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! 🙂 

~heathernicoale
2 ACCEPTED SOLUTIONS

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.

View solution in original post

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)

 

View solution in original post

8 REPLIES 8
kcantor
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

~heathernicoale

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.

 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
Continued Contributor
Continued Contributor

@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
    )
)


 

~heathernicoale

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

~heathernicoale

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)

 

@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. 🙂 

~heathernicoale

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.