Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
=if([Exclude Column]=0,"Yes","No")
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?
============================
Error Message:
============================
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.
============================
Call Stack:
============================
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitUpdates()
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.
Is this a calculated column or calculated measure?
If it is a calculated column, what is the datatype set to?
Is this a calculated column or calculated measure?
If it is a calculated column, what is the datatype set to?
Phil,
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |