Microsoft

Relationship Functions in Power BI

Man using tablet to look at charts

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.

2023 01 05 15 23 34 Untitled Power Bi DesktopThe 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.

2023 01 05 15 31 17 Untitled Power Bi DesktopThe 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):

2023 01 05 15 31 17 Untitled Power Bi DesktopIn 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

2023 01 05 15 33 53 Untitled Power Bi Desktop

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Prashant Parkhedkar

Prashant is an Associate Technical Consultant at Perficient in Nagpur. He is a Microsoft Certified Power BI Data Analyst Associate with over 3 years of experience. He is enthusiastic and eager to dive into the world of business analytics.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram