DAXDAX Trainer

DAX Concept

Leveraging RELATED for Cross-Table Calculated Columns in DAX

Understand and apply the DAX RELATED function to create calculated columns that retrieve values from related tables, including handling blank values.

IntermediateRELATED

Concept Breakdown

The RELATED function in DAX is used within a calculated column to retrieve a value from a column in another table that is on the 'one' side of a one-to-many relationship. It navigates the relationship automatically, provided that a relationship already exists between the tables. This allows you to enrich your current table with contextually relevant data from a dimension table, enabling more complex calculations and insights directly within the data model.

What You Will Learn

  1. Understand when to use RELATED in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided 'Music Tours - Related.pbix' file.

  2. Open the 'Music Tours - Related.pbix' file in Power BI Desktop.

  3. In the 'Tour' table, add a new calculated column named 'Artist age at tour start'. This column should subtract the 'Year formed' from the 'Artist' table (using RELATED) from the 'Start year' in the 'Tour' table, then add 1 to the result.

  4. Add another calculated column to the 'Tour' table, named 'Artist age at tour end'. This column should calculate the difference between the 'Year formed' from the 'Artist' table and the 'End year' from the 'Tour' table, also adding 1 to the result.

  5. Add a third calculated column to the 'Tour' table called 'Years between tour end and band break up'. This column should initially calculate the difference between the 'Year disbanded' column in the 'Artist' table (using RELATED) and the 'End year' column in the 'Tour' table.

  6. Modify the 'Years between tour end and band break up' column's formula to return BLANK if the 'Year disbanded' column in the 'Artist' table is blank. Otherwise, it should perform the original calculation.

  7. Display the results of these new calculated columns in a visual (e.g., a table visual) to verify the outputs.

  8. Save and close the report.

Starter DAX

Artist age at tour start = Tour[Start year] - RELATED('Artist'[Year formed])

Expected Outcome

Three new calculated columns will be added to the 'Tour' table:

  • 'Artist age at tour start': Contains numerical values representing the band's age at the beginning of each tour.
  • 'Artist age at tour end': Contains numerical values representing the band's age at the end of each tour.
  • 'Years between tour end and band break up': Contains a mix of positive and negative numbers where the band has disbanded, and BLANK values where the 'Year disbanded' information is missing from the 'Artist' table.
Checking your sign-in status...