Symmetric matrix to see how many components are in common for each pair of products

[Francesco95]

Hi all,

I have a table with Final products and Component relationship ( the real table contains 300.000+ rows for 500 products)

Product	Component
Product 1	Comp 1
Product 1	Comp 2
Product 1	Comp 3
Product 2	Comp 4
Product 2	Comp 2
Product 2	Comp 3
Product 3	Comp 1
Product 3	Comp 6
Product 3	Comp 2

I would like to obtain a Symetric matrix that show for each pair of Products how many components are in common.

Like this:

	Product 1	Product 2	Product 3
Product 1	3	2	2
Product 2	2	3	1
Product 3	2	1	3

And also a Symetric matrix that show  how many unique components are needed for each pair of Products

Like this:

	Product 1	Product 2	Product 3
Product 1	3	4	4
Product 2	4	3	5
Product 3	4	5	3

I am fairly new to DAX and ask if you have any suggestions for making these measures.

Thanks a lot!

[v-stephen-msft · Accepted solution]

Hi @Francesco95 ,

1.Duplicate the an original table, then use the CROSSJOIN function. When using this function, make sure that the column names of the two tables are different.

Table 2 = CROSSJOIN('Table','Table (2)')

2.Create two measures.

Measure = 
CALCULATE (
    COUNTROWS ( 'Table 2' ),
    FILTER ( 'Table 2', [XComponent] = [YComponent] )
)

Measure 2 = 
VAR _total =
    CALCULATE (
        COUNT ( 'Table'[XComponent] ),
        FILTER ( 'Table', 'Table'[XProduct] = MAX ( 'Table 2'[XProduct] ) )
    )
        + CALCULATE (
            COUNT ( 'Table'[XComponent] ),
            FILTER ( 'Table', 'Table'[XProduct] = MAX ( 'Table 2'[YProduct] ) )
        )
RETURN
    _total - [Measure]

The result is this

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Francesco95]

I tried to duplicate the table and use the following measure, it works with 10 test products but if I use the whole dataset 600+ products and 14000+ components it gets very slow and eventually fails due to memory limitations.

Component_product = 
VAR var1=
    CALCULATETABLE(
        DISTINCT(
            Test_table[Component]),
    REMOVEFILTERS(Test_table_1))

VAR var2=
    CALCULATETABLE(
        DISTINCT(
            Test_table_1[Component]),
    REMOVEFILTERS(Test_table))

RETURN
    VAR p1 = COUNTROWS(var1)
    VAR p2 = COUNTROWS(var2)
    VAR p1_p2 = COUNTROWS(DISTINCT(UNION(var1,var2)))
    RETURN
        DIVIDE(p1+p2-p1_p2,p1_p2)