Reply
Frequent Visitor
Posts: 12
Registered: ‎12-18-2015
Accepted Solution

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?

 

 


Accepted Solutions
Established Member
Posts: 193
Registered: ‎09-30-2015

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

View solution in original post


All Replies
Established Member
Posts: 193
Registered: ‎09-30-2015

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
Frequent Visitor
Posts: 12
Registered: ‎12-18-2015

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!

Established Member
Posts: 193
Registered: ‎09-30-2015

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.

Frequent Visitor
Posts: 13
Registered: ‎08-05-2015

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

Established Member
Posts: 193
Registered: ‎09-30-2015

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

Highlighted
Frequent Visitor
Posts: 10
Registered: ‎10-05-2016

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"