Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tryan
Frequent Visitor

DAX if statement syntax error

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)

============================

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Is this a calculated column or calculated measure?

 

If it is a calculated column, what is the datatype set to?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Is this a calculated column or calculated measure?

 

If it is a calculated column, what is the datatype set to?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark what if this was a calculated measure?

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:

 

https://support.office.com/en-us/article/Set-the-data-type-of-a-column-in-Power-Pivot-e62c582e-824f-...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.