The Essentials of DAX Relationship Functions

DAX Relationship Functions play a crucial role in effective data modeling. These functions help us understand the connections between different tables in our data model, enabling complex calculations and extracting valuable insights.

Establishing relationships within our data models is vital for ensuring the reliability and coherence of our data. These relationships link various tables, allowing for the creation of dynamic and insightful reports. As we develop Power BI reports, grasping these relationships becomes essential, as they influence how data filtering and aggregation are performed throughout our reports.

By using DAX Relationship Functions, we can tailor and manipulate these relationships to meet our analytical needs. These functions are especially beneficial when integrating data from multiple sources into a unified view. Mastering these functions can dramatically enhance our data analysis capabilities.

For comprehensive details on these functions, refer to the DAX Function Reference documentation.

Related Resources

To kickstart your experimentation, a Power BI report is available, pre-loaded with the same data discussed here! Don’t just read; actively engage with DAX in Power BI. Access the sample report here:

GitHub - EMGuyant/powerbi-dax-functions-series

Fetching Related Values with RELATED

The RELATED function in DAX is crafted to retrieve a related value from another table, and its syntax is quite straightforward:

RELATED(column_name)

In this instance, column_name refers to the column from the related table that we wish to extract. This function is particularly useful in calculated columns, especially when we need to reference data from a lookup table.

For the RELATED function to work, a relationship must exist between the two tables. The function will then navigate the established many-to-one relationship and return the specified column from the related table. Additionally, the RELATED function requires row context.

Consider our Sales table, which includes a ProductID that establishes a relationship with the Products table. We can introduce the Product field from the Products table into our Sales table using the following DAX formula:

Product Category = RELATED(Products[Product])

This DAX expression adds a new calculated column to our Sales table, displaying the product category for each sales record, making the sales data more informative and easier to analyze.

Furthermore, we can utilize the RELATED function alongside the existing relationship between our Sales and Regions tables to filter sales specifically for the United States. Here’s how we can define this measure:

United States Sales =

SUMX(

FILTER(

Sales,

RELATED(Regions[Region]) = "United States"

),

Sales[Amount]

)

This measure is more transparent and informative than filtering directly on the RegionID field in the Sales table. Using RELATED within our FILTER function enhances the readability of our measure, clearly indicating what it calculates.

The RELATED function serves as a robust tool for enriching our data models by seamlessly incorporating related information, ultimately allowing us to generate more comprehensive reports.

Navigating Related Tables with RELATEDTABLE

The RELATEDTABLE function in DAX enables us to access and summarize a related table based on the current row context. This function is particularly beneficial when summarizing or conducting calculations on data from a related table. Its syntax is as follows:

RELATEDTABLE(table_name)

Here, table_name denotes the existing related table we wish to retrieve. Note that table_name cannot be an expression.

For instance, if we want to calculate the total sales amount for each product, we can apply the RELATEDTABLE function to create a new calculated column in our Products table:

Total Sales by Product =

SUMX(

RELATEDTABLE(Sales),

Sales[Amount]

)

In this DAX expression, we sum the Amount column from the Sales table for each product. The RELATEDTABLE function fetches all rows from the Sales table that relate to the current product row in the Products table, and SUMX calculates the total for these rows.

With RELATEDTABLE, we can navigate and conduct calculations across related tables, allowing for a more granular and insightful analysis of our data.

Activating Inactive Relationships with USERELATIONSHIP

The USERELATIONSHIP function in DAX is intended to activate an inactive relationship between tables within a data model. This function becomes essential when a table has multiple relationships with another table, and we need to toggle between these relationships for various calculations. Its syntax is as follows:

USERELATIONSHIP(column1, column2)

In this context, column1 represents an existing column, typically from the many side of the relationship, while column2 denotes an existing column from the one or lookup side of the relationship.

The USERELATIONSHIP function does not return a value and can only be utilized in functions that accept a filter as an argument (e.g., CALCULATE, TOTALYTD). This function leverages existing relationships within the data model and cannot be applied when row-level security is enforced for the table containing the measure.

Let’s consider a scenario where we want to compute the number of employees who have exited the organization based on their end dates utilizing the USERELATIONSHIP function.

The Employee table contains each employee's StartDate and EndDate, which are used to establish a relationship with the DateTable. The relationship with StartDate is active, while the relationship with EndDate is inactive.

We can create our Employee Separations measure using the following DAX formula:

Employees Separations USERELATIONSHIP =

CALCULATE(

COUNT(Employee[EmployeeID]),

USERELATIONSHIP(Employee[EndDate], DateTable[Date]),

NOT(ISBLANK(Employee[EndDate]))

)

This measure counts the number of employees who have left the organization based on their EndDate by activating the previously inactive relationship between Employee[EndDate] and DateTable[Date], ensuring that it only counts employees with a recorded EndDate.

To appreciate the utility of USERELATIONSHIP, we can compare the results of this measure with another that does not activate the inactive relationship.

Employee Separations No USERELATIONSHIP =

CALCULATE(

COUNT(Employee[EmployeeID]),

NOT(ISBLANK(Employee[EndDate]))

)

In the No USERELATIONSHIP measure, we attempt to calculate the number of employees who left based on EndDate. However, without activating the relationship, the active relationship is used for the calculation context.

Among the 9 employees who departed, we observe that for 2022, the No USERELATIONSHIP measure counts 8 employees who started in that year instead of the 3 who left.

Controlling Cross-Filtering Behavior with CROSSFILTER

The CROSSFILTER function in DAX allows us to manage the direction of cross-filtering between two tables in our data model. This function enables us to specify whether the filtering direction is one-way, both ways, or nonexistent, thus controlling the data flow between our tables. This feature is particularly valuable in complex models where bidirectional filtering may yield unexpected results. Its syntax is as follows:

CROSSFILTER(column1, column2, direction)

The parameters column1 and column2 are akin to those in USERELATIONSHIP, with column1 representing the many side of the relationship and column2 the one side.

The direction parameter indicates the cross-filter direction to be applied and must be one of the following values:

• None — no cross-filtering occurs along this relationship
• Both — filters on either side filter the other side
• OneWay — filters on one side of the relationship filter the other side (not applicable for one-to-one relationships and not recommended for many-to-many relationships)
• OneWay_LeftFiltersRight — filters on the column1 side filter the column2 side (not applicable for one-to-one or many-to-one relationships)
• OneWay_RightFiltersLeft — filters on the column2 side filter the column1 side (not applicable for one-to-one or many-to-one relationships)

The CROSSFILTER function does not return a value and is only usable within functions accepting a filter as an argument (e.g., CALCULATE, TOTALYTD). When relationships are set up in our data model, we define the cross-filtering direction, and utilizing the CROSSFILTER function allows us to override this setting.

Let’s explore a scenario where we want to analyze the distinct products sold and the total sales amount by month and year. We begin by creating a Distinct Product Code Count measure:

Distinct Product Code Count =

DISTINCTCOUNT(Products[Product Code])

If we include this measure in a table visual, we may encounter an issue with the count. The count returns the total number of product codes instead of the intended distinct products sold during that month.

This is due to the one-to-many relationship (Product-to-Sales) with a single-direction filter (i.e., Product filters Sales). This default configuration prevents our Sales table from filtering the Product table, leading to incorrect results.

We could resolve this by altering the cross-filtering direction property on the Product-Sales relationship. However, this would impact how filters operate for all data between these two tables, which may not be ideal.

An alternative is to harness the capabilities of the CROSSFILTER function. We can employ this function to modify how the Product-Sales relationship functions within a new measure:

Distinct Product Code Count Bidirectional =

CALCULATE(

[Distinct Product Code Count],

CROSSFILTER(Sales[ProductID], Products[ProductID], Both)

)

Adding this new measure to our table will yield the expected results. This measure collects all sales records in the current context (e.g., January 2022), then filters the Product table to include only related products, ultimately returning a distinct count of the products sold.

This measure can now be utilized alongside the Sales Amount to analyze our sales data, providing insights into the number of different products sold each month.

By leveraging CROSSFILTER, we maintain control over our data relationships, ensuring that our reports deliver the precise insights we require without unintended data flows. This level of control is essential for constructing robust and reliable Power BI models.

In Conclusion

DAX relationship functions are invaluable tools that enhance our capability to manage and analyze data in Power BI effectively. We have examined how these critical functions empower us to connect and manipulate data and relationships within our data model. By understanding when and how to utilize these functions, we can create dynamic, accurate, and insightful reports. Here’s a brief recap of the functions discussed:

• RELATED simplifies data retrieval by accessing values from a related table, making our data more informative and easier to analyze.
• RELATEDTABLE allows us to navigate and summarize related tables, providing deeper insights into our data.
• USERELATIONSHIP offers the flexibility to activate inactive relationships, facilitating more complex and context-specific calculations.
• CROSSFILTER enables us to control the direction of cross-filtering between tables, ensuring our data flows exactly as needed.

For a deeper dive into these functions, refer to the DAX Relationship Function reference documentation.

Relationship functions (DAX) - DAX

By integrating these functions into our DAX toolkit, we enhance our ability to create adaptable and robust data models, ensuring that our reports are both visually appealing and rich in informative content.

To discover additional function groups that elevate our data analysis, explore the Dive into DAX series, where each post presents an opportunity to refine your data analytics and Power BI reports.

Dive into DAX - Ethan Guyant

Thank you for your time! Stay curious, and until next time, happy learning!

As Albert Einstein wisely noted, “Anyone who has never made a mistake has never tried anything new.” Embrace mistakes as part of the learning process; practice fosters improvement. Continually experiment, explore, and push your boundaries with real-world scenarios.

If this piqued your interest, keep that enthusiasm alive and check back often. Better yet, don’t miss out on any posts by subscribing! Each new post is a chance to learn something new.