There is an existing limitation in the current version of DAX, regarding what names you provide to variables in a DAX expression: a variable name cannot be the name of a table in the data model.
This is not a big issue if you create DAX expressions after importing all the tables you need. However, this can be a serious issue when importing new tables into an existing model. For example, imagine you have created a measure like the following one in a data model. Beware, this is NOT the best approach to compute new customers. We are not interested in performance here, just observe the code:
Sales Returning Customers := VAR FirstDateVisible = MIN ( 'Date'[Date] ) VAR CurrentCustomers = VALUES ( Sales[CustomerKey] ) VAR PreviousCustomers = CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), 'Date'[Date] < FirstDateVisible ) VAR ReturningCustomers = INTERSECT ( CurrentCustomers, PreviousCustomers ) VAR Result = CALCULATE ( [Sales Amount], ReturningCustomers ) RETURN Result
You deploy this model and start creating reports using the Sales Returning Customers measure. So far, so good. One day, you need to extend the data model by importing a new table that you decide to name ReturningCustomers. As soon as you import the new table named ReturningCustomers, your Sales Returning Customers measure stops working. The reason is that the ReturningCustomers variable generates a name conflict with the table that has the same name, as you can see from the error message.
‘ReturningCustomers’ is a table name and cannot be used to define a variable.
You might think, “Where is the problem? We can just refactor variable names if a new table breaks something in the data model”. Well, this would be true (albeit cumbersome) if all the measures were embedded in the model; but Power BI allows connecting to an external model (e.g. another Power BI report or an Analysis Services database) and then creates local measures. You have no control over this, and you are not able to know how many measures you are going to break in existing reports every time you publish an update to an existing data model. You should already know that removing or renaming existing tables/columns/measures could be dangerous, but it’s hard to predict that a new table name might break existing reports. You know it will happen, you just don’t know when. Hint: it will be sooner than expected.
Microsoft developers are aware of the issue and will hopefully change the DAX syntax, thus allowing a variable to have the same name as any existing table. This change is backward compatible with any existing measures and should not have any side effect. However, I don’t know when this update will be implemented and released, so it is better to be aware of the issue in the meantime.
Therefore, is there a way to safely name variables to avoid possible naming conflicts? Clearly, this is not an exact science. For example, Microsoft implements measure names using a double underscore as a prefix in quick measures. The previous measure could be written this way:
Sales Returning Customers := VAR __FirstDateVisible = MIN ( 'Date'[Date] ) VAR __CurrentCustomers = VALUES ( Sales[CustomerKey] ) VAR __PreviousCustomers = CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), 'Date'[Date] < FirstDateVisible ) VAR __ReturningCustomers = INTERSECT ( __CurrentCustomers, __PreviousCustomers ) VAR __Result = CALCULATE ( [Sales Amount], __ReturningCustomers ) RETURN __Result
You might want to use a single underscore as a prefix instead of two. The reason why Microsoft chose two underscores is probably because they wanted to minimize the risk of a name conflict with any table that starts with an underscore. After all, using that prefix could be a technique used to put a table name at the beginning in models with many tables.
One side effect of there being a prefix to variable names is that you can clearly identify a variable in an expression even if you are not using an advanced editor. Alberto and I discussed about this for hours, but we decided not to promote this coding style as a best practice. There are several rational reasons for that. The main reason is purely aesthetic. We do not like prefixes in names. We simply use more descriptive names that make the code easier to read and minimize possible conflicts. For example, if a variable name always uses multiple words, a table name will never conflict as long as table names have a space separating the different words (a variable name cannot have spaces). Nevertheless, we agreed that we should discuss this publicly, because in certain conditions you might decide that you want to use prefixes. For example:
- You generate DAX code automatically based on templates, through macros or other coding techniques. Variables in templates should always have a prefix, just as in quick measures.
- You are working on huge models with multiple developers and it is hard to keep consistent naming styles.
We did not see many real-world cases of the name conflict issue described in this post. However, this event could occur more frequently with the increasing adoption of Power BI and a larger use of variables in expressions. It is better to be aware of the risk, at least until Microsoft fixes the naming conflict.