DAXDAX Trainer

DAX Concept

Retrieving Related Data with DAX's RELATED Function

Learn how to use the RELATED DAX function to retrieve values from a related table into a calculated column, and effectively handle cases where no related value is found using ISBLANK.

IntermediateRELATEDISBLANK

Concept Breakdown

The RELATED function is a scalar DAX function that navigates existing many-to-one or one-to-one relationships to retrieve a single value from a column in the 'one' side of a relationship. It is crucial for enriching tables with information from related lookup tables. The ISBLANK function is often used in conjunction with RELATED to check if the RELATED function returned a blank value (indicating no match was found) and provide a fallback value or custom text, preventing blanks from appearing in the report.

What You Will Learn

  1. Understand when to use RELATED, ISBLANK 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 Power BI file.

  2. Open the Power BI report.

  3. In Model view, hide all tables except the 'Towns' table by right-clicking on each table to be hidden.

  4. Add a new calculated column to the 'Towns' table and name it 'Quadrant'.

  5. Enter the DAX formula for 'Quadrant' using RELATED to fetch the 'Quadrant Name' from the 'Quadrant' table, and ISBLANK to display "No quadrant found" if no related quadrant is found.

  6. Add another new calculated column to the 'Towns' table and name it 'Region'.

  7. Enter the DAX formula for 'Region' using RELATED to fetch the 'Region Name' from the 'Region' table, and ISBLANK to display "No region assigned" if no related region is found.

  8. Create a new table visual on your report page.

  9. Add the 'Town Name', 'Quadrant' (from the Towns table), and 'Region' (from the Towns table) fields to the table visual.

  10. Verify that towns without a corresponding region or quadrant display the custom 'No region assigned' or 'No quadrant found' text instead of blanks.

  11. Save your revised Power BI file as 'Where is Owlton'.

Starter DAX

Quadrant = 
Region =

Expected Outcome

A table visual displaying 'Town Name', 'Quadrant', and 'Region' for each town. For towns where a related quadrant or region is not found, the custom text 'No quadrant found' or 'No region assigned' will be displayed in the respective columns.

Checking your sign-in status...