cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Matrix with Count by matching rows and columns

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


Match.jpg

 


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! 
 
 

 

 

 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Matrix with Count by matching rows and columns

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Matrix with Count by matching rows and columns

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User II
Super User II

Re: Matrix with Count by matching rows and columns

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Community Support
Community Support

Re: Matrix with Count by matching rows and columns

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"

724.PNG

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors