- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 04:42 AM

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?

Solved! Go to Solution.

Accepted Solutions

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 05:44 AM

Excel | =IF(test, value_if_true, value_if_false) |

Power Query | =if test then value_if_true else value_if_false |

All Replies

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 05:44 AM

Excel | =IF(test, value_if_true, value_if_false) |

Power Query | =if test then value_if_true else value_if_false |

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 05:45 AM

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!

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 05:59 AM

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.

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 07:38 AM

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.

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-18-2015 08:20 AM

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

## Re: IF - Formula in Add Custom Column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2016 05:33 AM

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"