Syntax error when using a comma in the formula
This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
Question
I have a modeled account with this code: ACCT.Personnel.Salary. I want to filter by the Position dimension in a Custom account, so the formula would look something like this: ACCT.Personnel.Salary[Position= RN, Triage]. I am using the formula assistant, but I keep getting a syntax error. How can I get this formula to work?
Answer
The issue is most likely with the comma in the dimension value name. Here are a few things that you could try:
- In the Define Dimensions screen, change the dimension value name so that it does not contain a comma. When you update a dimension value name, it will update in all formulas where it is referred to.
- You could try using a backslash to escape the comma in the syntax:
ACCT.Personnel.Salary[Position=RN,\ Triage]If you modify the formula, this will have to be re-added because it disappears after you save the formula.
This problem does not occur in the modeled sheets because the syntax is slightly different there.
We recommend solution #1. In general, users should try to avoid using commas/semicolons in dimension value names, but the system will not prevent the entry.