The using of Cartesian products in many-to-many relational tables

[v-jingzhan-msft]

Scenario: 

In Power BI, we will encounter the many-to-many relationships frequently. Typically, when faced with this situation, we always create a bridge table to establish connections and process the data. However, creating a bridge table will involve the calculation of more than three tables, and it may lead to circular dependencies sometimes. As an alternative, in certain scenarios, we can explore using the Cartesian Product to handle the data. Let's look at a sample in the next sections.

Sample Data:

There are two tables in the sample:

Table 1 is the Order table, including Product Name, Date, Order Quantity, which provides the order quantity of the three products in March and April.

Table2 is the USAGE table, including Part Number, Quantity, Product Name, which shows the base numbers of different products at different parts.

Expected Result:

We need to multiply the quantity of each part by the Order Quantity of different products in each month. (For example, for Part 1 in March 2024, product 1 =  1000 * 1; product 2 =5000 * 2 = 10000)

The subtotal row of each part should be the sum of above calculation(Order Quantity * Quantity) of each product. (For example, Part 1 = 1000 + 10000 = 11000 but not 3 * 6000 = 18000 )

We hope to achieve the results into a matrix visual like below. The "Total Qty" is what we want.

How:

The Order table and the USAGE table have the same field: Product Name, it is easy to think about creating a relationship between the 2 tables. And use RELATEDTABLE(), CALCULATE() and other such functions to get the outcome.

But the two tables can be linked by only many-to-many relationship.

About many-to-many relationship, we can easily think about adding a bridging table to store associated entities, just like this:

This is a common solution about many-to-many, but there are also some drawbacks that bi-directional relationships can impact negatively on performance. Further, attempting to configure a bi-directional relationship could result in ambiguous filter propagation paths. And functions that will be used next such as relatedtable(), calculate() and so on will transform row context into filter context. While this approach usually yields correct answers, it can be cumbersome and time-consuming.

In this way, consider to use the Cartesian products may be a good option. So, what are the Cartesian products?

For example, here are two tables:

The Table Colors contains Color and Pattern:

The table Stationery contains Font and Presentation:

The Cartesian products of the two tables = Crossjoin(‘Colors’,’Stationery’):

The Cartesian product can display all matching results for all fields from two tables. For the above scenario, we can use the GENERATE() or CROSSJOIN() functions to expand the data. Click ‘New table,’ then create a Cartesian product table using the CROSSJOIN() function. If the two tables have columns with the same name, you’ll receive a prompt during table creation that the resulting table cannot have duplicate columns. In this case, we can use the SELECTCOLUMNS() function to rename the duplicate column ‘Product Name’.

CROSSJOIN(
        SELECTCOLUMNS(
            'USAGE',
            'USAGE'[Part Number],
            'USAGE'[Product Name],
            'USAGE'[Quantity]
        ),
        'ORDERS'
    )

It is easy to see that there are many useless values in this table, a filter is needed:

'Table' = FILTER(
        CROSSJOIN(
            SELECTCOLUMNS(
                'USAGE',
                'USAGE'[Part Number],
                'USAGE'[Product Name],
                'USAGE'[Quantity]
            ),
            'ORDERS'
        ),
        'USAGE'[Product Name] = 'ORDERS'[Product Name]
    )

The next step is to add a new calculated column:

Total Qty = 'Table'[USAGE_Quantity]*'Table'[Order Quantity] 

Then create a matrix with these values, the result is as follow:

Summary:

In many-to-many relationships within tables, we typically use bridge tables for connections. Then, we apply functions like relatedtable(), calculate(), and others for filtering and calculations. This will transform row context into filter context. And the advantage of using Cartesian product tables lies in the ability to apply filters across multiple tables simultaneously, simplifying our processing of writing DAX. However, it’s important to note that Cartesian products are not suitable for large datasets due to their computational load, which is the product of the data volume from multiple tables and may impact performance.

Author: Azik X. 

Reviewer: Ula and Kerry