Relationship functions in Power BI allow you to access fields within DAX measures or calculated columns through either physical or virtual relationships between tables.
Common Use Cases for Relationship Functions in Power BI
- Defining calculated columns or measures using fields from related tables.
- Handling relationships between calendars and multiple date fields (transaction date, stock date, due date, etc.)
- Traversing inactive relationships in the model or modifying relationship filter behavior.
- Defining calculated, virtual relationships when physical relationships aren’t possible due to table granularity.
Physical vs. Virtual Relationships
There are two key types of table relationships: PHYSICAL and VIRTUAL
Physical Relationship
- Physical relationships are manually created and visible in your data model.
The above picture shows physical relationships:
- Visible links between tables (typically 1:* cardinality).
- Can be active or inactive.
- Can be accessed using DAX functions like RELATED, RELATEDTABLE, or USERELATIONSHIP.
- Best way to connect tables (but not always possible).
Virtual Relationship
- Virtual relationships are temporary and defined using DAX expressions.
The above picture shows a virtual relationship:
- Defined using DAX expressions.
- Used when a physical relationship doesn’t exist or cannot be created directly.
- Often used to connect tables with different levels of granularity (i.e. daily sales vs. monthly budgets/goals).
- Can be accessed using DAX functions like TREATAS.
RELATED
RELATED() Returns a value from a related table in the data model.
= RELATED(ColumnName)
ColumnName is the column name you want to retrieve values from (must reference a table on the “one” side of a many-to-one relationship).
Examples:
- RELATED(‘Dim Product’[current_cost])
- RELATED(‘Dim Customer’[home_store])
NOTE: The RELATED function doesn’t really perform an operation, it just “opens the door” to access columns from an expanded table.
RELATEDTABLE
RELATEDTABLE() Returns a related table, filtered to only include the related rows.
= RELATEDTABLE(Table)
The physical table you want to retrieve rows from (must reference a table on the “many” side of a many-to-one relationship).
RELATEDTABLE is commonly used with aggregators like COUNTROWS, SUMX, AVERAGEX, etc.
Examples:
- COUNTROWS(RELATEDTABLE(‘Fact Food’))
- SUMX(RELATEDTABLE(‘Fact Food’), [Quantity Sold] * [Unit Price])
NOTE: RELATEDTABLE is a shortcut for CALCUATETABLE (with no logical expression) and performs a context transition from row context to filter context, in order to return only the rows which satisfy the filter condition(s).
USERELATIONSHIP
USERELATIONSHIP() Specifies an existing relationship to be used in the evaluation of a DAX expression, defined by naming, as arguments, the two columns that serve as endpoints.
=USERELATIONSHIP(ColumnName1, ColumnName2)
ColumnName1 Foreign (or primary) key of the relationship.
Examples:
- Fact Food[Baked_Date]
- Dim Calendar[Transaction Date]
ColumnName2 Primary (or foreign) key of the relationship.
Examples:
- Dim Calendar[Transaction Date]
- Fact Food[Baked_Date]
USERELATIONSHIP can only be used in functions which accept a filter parameter (CALCULATE, TOTALYTD, etc.)
NOTE: If you have multiple date columns connected to a single calendar table, USERELATIONSHIP is a great way to force measures to use inactive relationships without having to manually activate them in your model.
CROSSFILTER
CROSSFILTER() Specifies cross filtering direction to be used for the duration of the DAX expression. The relationship is defined by naming the two columns that serve as endpoints.
= CROSSFILTER(LeftColumnName, RightColumnName2, CrossFilterType)
The two columns you want to use. Left column is typically the “many” side and right column is typically the “one” side.
Examples:
- ‘Fact Sales'[customer_id]
- ‘Dim Customer'[customer_id]
Specifies the direction of the CROSSFILTER.
Examples:
- OneWay, Both, None
NOTE: Instead of bi-directional relationships, use CROSSFILTER to enable two-way filtering only in specific cases.
TREATAS
TREATAS() Applies the result of a table expression to filter columns in an unrelated table (essentially creating a new virtual relationship).
= TREATAS(TableExpression, ColumnName, [ColumnName], […])
A table expression which generates the set of columns to be mapped. Table expression must be based on physical table in data model.
Examples:
- TREATAS(VALUES(‘Dim Calendar’[Year_ID)…
- TREATAS(SUMMARIZE(‘Dim Calendar’, ‘Dim Calendar’[Year_ID], ‘Dim Calendar[Month]…
ColumnName The list of output columns (cannot be an expression).
The number of columns specified must match the number of columns in the table expression and be in the same order.
Examples:
- ‘Union Demo’[Year]
- ‘Union Demo’[Year], ‘Union Demo’[Month]
NOTE: Use physical relationships (or USERELATIONSHIP functions) whenever possible and only rely on TREATAS if you are unable to create a direct relationship between tables.
TREATAS (EXAMPLE):
In this case, we can’t create physical relationships to Union Demo since it’s at a different granularity level than the model’s other tables.
TREATAS allows us to create virtual, summarized versions of our tables to match the granularity that we need to form a valid relationship.
DAX Query
