Question

How can I sum values that appear in both of two Power BI tables (i.e., intersection logic)?

I need an automated way to sum values that appear in both tables using intersection logic in Power BI, ensuring that the data is consistently aggregated when new data is added.

Answer 1

To sum values that appear in both tables in Power BI (i.e., using intersection logic), you can use DAX's INTERSECT function combined with SUMX for aggregation. Here's how you can do it:

Example:

Let’s say you have two tables, Table1 and Table2, and you want to sum values from a column that exists in both tables, for rows that are common between them.

SumIntersection =
SUMX(
    INTERSECT(
        VALUES('Table1'[KeyColumn]),
        VALUES('Table2'[KeyColumn])
    ),
    'Table1'[ValueColumn]  // Or you can sum from 'Table2' as needed
)

Explanation:

• VALUES(): Returns a unique list of values from the specified column.

• INTERSECT(): Finds the intersection of the two columns, i.e., the common values between the two tables.

• SUMX(): Sums the values for the rows that are returned by the INTERSECT function.