cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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! Smiley Happy 

~heathernicoale
2 ACCEPTED SOLUTIONS

Accepted Solutions
Power BI Team JeffDuzak
Power BI Team

Re: How to create an IF statement in Power BI for reporting

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.

Power BI Team JeffDuzak
Power BI Team

Re: How to create an IF statement in Power BI for reporting

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)

 

8 REPLIES 8
kcantor Super Contributor
Super Contributor

Re: How to create an IF statement in Power BI for reporting

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




Re: How to create an IF statement in Power BI for reporting

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

~heathernicoale
Power BI Team JeffDuzak
Power BI Team

Re: How to create an IF statement in Power BI for reporting

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.

Re: How to create an IF statement in Power BI for reporting

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

~heathernicoale
Power BI Team JeffDuzak
Power BI Team

Re: How to create an IF statement in Power BI for reporting

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)

 

Re: How to create an IF statement in Power BI for reporting

@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. Smiley Happy 

 

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

~heathernicoale
arpihm Occasional Visitor
Occasional Visitor

Re: How to create an IF statement in Power BI for reporting

 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 .

Re: How to create an IF statement in Power BI for reporting

@arpihm - Hello! Smiley Happy 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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 99 members 1,545 guests
Please welcome our newest community members: