Edit

Column and measure references

  • Article

As a data modeler, your DAX expressions will refer to model columns and measures. Columns and measures are always associated with model tables, but these associations are different, so we have different recommendations on how you'll reference them in your expressions.

Columns

A column is a table-level object, and column names must be unique within a table. So it's possible that the same column name is used multiple times in your model—providing they belong to different tables. There's one more rule: a column name cannot have the same name as a measure name or hierarchy name that exists in the same table.

In general, DAX will not force using a fully qualified reference to a column. A fully qualified reference means that the table name precedes the column name.

Here's an example of a calculated column definition using only column name references. The Sales and Cost columns both belong to a table named Orders.

Profit = [Sales] - [Cost]

The same definition can be rewritten with fully qualified column references.

Profit = Orders[Sales] - Orders[Cost]

Sometimes, however, you'll be required to use fully qualified column references when Power BI detects ambiguity. When entering a formula, a red squiggly and error message will alert you. Also, some DAX functions like the LOOKUPVALUE DAX function, require the use of fully qualified columns.

It's recommended you always fully qualify your column references. The reasons are provided in the Recommendations section.

Measures

A measure is a model-level object. For this reason, measure names must be unique within the model. However, in the Fields pane, report authors will see each measure associated with a single model table. This association is set for cosmetic reasons, and you can configure it by setting the Home Table property for the measure. For more information, see Measures in Power BI Desktop (Organizing your measures).

It's possible to use a fully qualified measure in your expressions. DAX intellisense will even offer the suggestion. However, it isn't necessary, and it's not a recommended practice. If you change the home table for a measure, any expression that uses a fully qualified measure reference to it will break. You'll then need to edit each broken formula to remove (or update) the measure reference.

It's recommended you never qualify your measure references. The reasons are provided in the Recommendations section.

Recommendations

Our recommendations are simple and easy to remember:

  • Always use fully qualified column references
  • Never use fully qualified measure references

Here's why:

  • Formula entry: Expressions will be accepted, as there won't be any ambiguous references to resolve. Also, you'll meet the requirement for those DAX functions that require fully qualified column references.
  • Robustness: Expressions will continue to work, even when you change a measure home table property.
  • Readability: Expressions will be quick and easy to understand—you'll quickly determine that it's a column or measure, based on whether it's fully qualified or not.

Related content