DAX Integration of Customer and Sales Tables

DAX Formula for Combining Customer and Sales Tables

Objective

To create a DAX formula that combines information from a Customer table and a Sales table, allowing for consolidated analysis of sales performance linked to customers.

Assumptions

• The Customer table contains customer details with a unique identifier named CustomerID.
• The Sales table contains sales transaction details, including a CustomerID column to correlate with the Customer table and additional sales information.

DAX Formula

To combine the two tables using DAX, you can use the RELATED function in a calculated column of the Sales table to bring in data from the Customer table. Here is an example formula that extracts the CustomerName from the Customer table and includes it in the Sales table:

CustomerName = RELATED(Customer[CustomerName])

Explanation

• RELATED Function: This function fetches the related value from another table based on an existing relationship. In this case, it retrieves the CustomerName from the Customer table where CustomerID matches that in the Sales table.
• Calculated Column: The above formula should be placed in the Sales table as a new calculated column. Once created, it will automatically populate with the corresponding customer names based on the defined relationship.

Practical Illustration

Assuming we have the following sample data:

Customer Table:

Sales Table:

After applying the DAX formula, the Sales table will output:

Summary

• The DAX formula provided allows for seamless integration of customer names into sales records, facilitating management and analysis of sales data linked to specific customers.
• It demonstrates how to leverage relationships within a data model effectively.

Additional Learning

Consider exploring courses on the Enterprise DNA Platform for a deeper understanding of DAX functions and data modeling practices.