IF - Formula in Add Custom Column

[leinad13]

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?

[leinad13]

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]

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.

[itchyeyeballs · Accepted solution]

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

[Zwakele]

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"

[joeort]

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.

[itchyeyeballs]

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