I am trying to write a simple if statement in power pivot using DAX. I wrote many if statement that return either a 1 or a 0 depending on whether or not the logical statement is true or false. However, when I try return a text value, such as:
Then I get the error message below. The same formula works with this syntax: =if([Exclude Column]=0,1,0)
Why is this error message appearing? I've tried testing the logical statement on text columns and number columns, and there's seems to be no difference. What's wrong with the formula?
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 23, ”.
The calculated column 'Query[CalculatedColumn1]' contains a syntax error. Provide a valid formula.
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.Update(UpdateOptions updateOptions)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.UpdateAddedColumns(List`1 addedColumns, List`1 oldExpressions, IList`1 colIndices)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions)
at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)
Solved! Go to Solution.
Thanks for your quick reply. That was the issue. I was adding a calculated column to a dataset, and it was defaulting to a number format for some reason when it needed to be text. When I reopened the file this morning, for some reason I didn't need to even reformat the calculated column. When I wrote the formula it automatically sensed the column format needed to be text. But here is are the steps for reformatting a column if anyone else views this thread: