DAX Function to Multiply Two Columns from Different Tables

Problem Definition

Create a DAX measure that multiplies a column from one table with a column from another table.

Solution Overview

The solution involves creating a DAX measure that performs the multiplication of the values from two different columns belonging to two different tables. We need to ensure that the tables have a relationship for proper row context calculation.

Step-by-Step Solution

1. Data Model Preparation

Ensure that the two tables (Table1 and Table2) are properly related in the data model by a common key, such as ID.

2. Creating the Measure

Write a DAX measure to perform the multiplication.

DAX Measure: MultiplyColumns

MultiplyColumns = 
VAR Table1Col = RELATED(Table1[Column1])
VAR Table2Col = Table2[Column2]

RETURN
    SUMX(Table2, Table1Col * Table2Col)

Explanation

1. Variable Definitions:

   • Table1Col: Uses the RELATED function to fetch values from Column1 in Table1 as the measure will be in the context of Table2.
   • Table2Col: Directly accesses Column2 in Table2.

2. SUMX Function:

   • Iterates over Table2 and computes the product of corresponding rows from Table1Col and Table2Col.
   • Aggregates (sums up) the products to return a single value.

Input Validation

Ensure:

• Table1 and Table2 are present in the data model.
• A relationship exists based on a common key, for example, ID.

Practical Example

Data Example

Assume Table1 has columns:

• ID
• Column1

And Table2 has columns:

• ID
• Column2

Data

Example data for Table1:

Example data for Table2:

Relationship

Establish a relationship between Table1 and Table2 using the ID column.

Measure Usage

When you add the measure MultiplyColumns to a visualization that respects the relationship between Table1 and Table2, it calculates the multiplication and sums up the results: [ \text{Sum of (102, 203) = 102 + 203 = 70} ]

Conclusion

The measure MultiplyColumns effectively multiplies corresponding rows from two different tables and aggregates the result, demonstrating a fundamental but frequently required DAX capability.

Note

For hands-on learning and deeper understanding of DAX, consider exploring relevant courses on the Enterprise DNA Platform.