Matrix with Count by matching rows and columns

[lgargallo]

Hello everyone!
Based on these data tables as an example:

I want to match products in pairs, it doesn't matter the order, for example, for Client "C": Case-Mouse, Case-Notebook, Mouse-Notebook, Mouse-Case, Notebook-Mouse, Notebook-Case. When It matches in a column and a row, It will count 1.

I tried many things (Always working with a Matrix as the visual object). I made a duplicated table for Products (Table 2) to put one as a column and the other as Row.

Match_Products =

       CALCULATE(COUNT('Table1'[Client]),

                      FILTER('Table1',

                       'Table1'[Client] ='Table1'[Client]&&

                       'Table1'[Product] =MAX('Table2'[Producto])&&

                       'Table1'[Product] =MAX('Table2 (2)'[Producto])

)

)

Also, I tried with LOOKUPTABLE, or using Power Query with Pivot Columns... but nothing works for me.

Any help would be appreciated. Thank you! 

[dax]

Hi @lgargallo ,

According to your expected output, I don't understand your logic, did you want to crossjoin table? If possible, could you please explain this in details? If you want to crossjoin table, you could use below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLNLy1OVYrViVZyAvK8UyuT8hOLUsACzkAB50SorDOKWhDPL78kNSk/Pxss4IIu4IQu4IpigCO6ZS4o0q5wq2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Product", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Client"}, #"Changed Type", {"Client"}, "Changed Type", JoinKind.FullOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Client", "Product"}, {"Client.1", "Product.1"})
in
    #"Expanded Changed Type"

Best Regards,
Zoe Zhi

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

[az38]

Hi @lgargallo 

try:

1. rename column Table2[Product] to Table2[Product2] 

2.

create a new table

Table = 
ADDCOLUMNS(
	CROSSJOIN('Table 1','Table 2'),
	"CrossCount", 
	CALCULATE(
		COUNTROWS('Table 1'),
		FILTER(
			ALL('Table 1'),
			'Table 1'[Client]=EARLIER('Table 1'[Client]) && 'Table 1'[Product] = EARLIER('Table 2'[Product2])
		)
	)
)

3. put into matrix Table[Product] as Rows, Table[Product2] as Columns and SUM CrossCount as Values

[amitchandak · Accepted solution]

@lgargallo seem very similar to Market Basket Analysis, check if these can help
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/