How to count number of rows containing text in a matrix

[jilee]

Hi all, 

I don't even know how to best describe the problem but I guess there are two parts to what I hope to accomplish here.

Let's say there is this Purchase table.

Purchase_Id	Product
1	A;
2	A; C;
3	C;
4	B;
5	A;B;C;

And a customer table.

Customer_Id	Purchase_Id
1	1
2	1
3	5
4	4
5	4
6	3
7	2

So first, I want to get a unique list of Products from the Purchase table. Then I want to see how many customers purchased each product. What I would like to see in matrix table is the following.

Product	Count of Customers
A	4
B	3
C	3

Product A appears in Purchase_Ids 1, 2 and 5, and there are 4 customers with Purchase_Ids 1, 2 and 5.

Product B appears in Purchase_Ids 4 and 5, and there are 3 customers with Purchase_Ids 4 and 5.

Product C appears in Purchase_Ids 2,3 and 5, and there are 3 customers with Purchase_Ids 2, 3 and 5.

I may be able to do the first step (getting the unique list of products) in python but is the second step possible in PowerBI? I don't see how I can build a relationship with the unique list of products even if I somehow was able to generate it.

Any suggestions?

Thank you very much!

J

[qqqqqwwwweeerrr · Accepted solution]

Hi @jilee 

Here are steps to achieve the required visual.

Step 1: import data split the column with delimiter in edit query

Step 2: unpivot the columns, remove blank and then if any exptra space only if required and colse and apply the edits 

step three create the relatioship in power bi

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards