cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leinad13 Frequent Visitor
Frequent Visitor

IF - Formula in Add Custom Column

I am attempting to add a simple column to show true or false if another column is greater than 2. I have written the formula as :

 

=IF([Spec]>2,true,false))

 

However, the edit window accepts it, but when i press OK - and go back to the query editor it wont accept it and says :

 

"Expression Error:The name 'IF' wasn't recognised. Make sure it's spelled correctly."

 

I dont understand, how it can be valid syntax - before clicking OK and then invalid afterwards?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
itchyeyeballs Established Member
Established Member

Re: IF - Formula in Add Custom Column

Excel=IF(test, value_if_true, value_if_false)
Power Query=if test then value_if_true else value_if_false
6 REPLIES 6
Highlighted
itchyeyeballs Established Member
Established Member

Re: IF - Formula in Add Custom Column

Excel=IF(test, value_if_true, value_if_false)
Power Query=if test then value_if_true else value_if_false
leinad13 Frequent Visitor
Frequent Visitor

Re: IF - Formula in Add Custom Column

I think i was being a bit of an eejit and trying to do this in Query editor instead of the data dashboard or whatever it is called.

 

Anyway, all sorted!

itchyeyeballs Established Member
Established Member

Re: IF - Formula in Add Custom Column

Should both work,

 

at a guess doing it in power query may help performance as it will only need to get calculated during import, I'm not sure if doing it within powerpivot will mean its being calculated everytime you refresh your visuals or not.

joeort Frequent Visitor
Frequent Visitor

Re: IF - Formula in Add Custom Column

If I wanted to compare a column against a hard coded date, how would I write that formula?

 

if date(2013,4,1)<[Date__c]then 1 else 0

Gives me an error that date isn't recognized.

 

 

2015-12-18_1037.png

itchyeyeballs Established Member
Established Member

Re: IF - Formula in Add Custom Column

Use the datevalue function in DAX to convert the hard coded date string to a date type

https://msdn.microsoft.com/en-us/library/ee634543.aspx

 

If you did this in poswer query the syntax is different 

https://msdn.microsoft.com/en-us/library/mt260703.aspx

Zwakele Frequent Visitor
Frequent Visitor

Re: IF - Formula in Add Custom Column

This worked for me. I was trying to evaluate if the value is >=500 000 or <500 000.

 

This formula didn’t work from PowerPivot: =IF([DisplayCost]>=500000,">=R500 000","<R500 000")

This Worked: if[DisplayCost]>=500000then">=R500 000"else"<R500 000"

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: 321 members 3,437 guests
Please welcome our newest community members: