Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jilee
Frequent Visitor

How to count number of rows containing text in a matrix

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_IdProduct
1A;
2A; C;
3C;
4B;
5A;B;C;

And a customer table.

Customer_IdPurchase_Id
11
21
35
44
54
63
72

 

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.

ProductCount of Customers
A4
B3
C3

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

 

 

1 ACCEPTED SOLUTION
qqqqqwwwweeerrr
Super User
Super User

Hi @jilee 

 

Here are steps to achieve the required visual.

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

qqqqqwwwweeerrr_0-1705120386948.png

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

qqqqqwwwweeerrr_1-1705120445398.png

step three create the relatioship in power bi

qqqqqwwwweeerrr_2-1705120577901.png

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

View solution in original post

1 REPLY 1
qqqqqwwwweeerrr
Super User
Super User

Hi @jilee 

 

Here are steps to achieve the required visual.

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

qqqqqwwwweeerrr_0-1705120386948.png

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

qqqqqwwwweeerrr_1-1705120445398.png

step three create the relatioship in power bi

qqqqqwwwweeerrr_2-1705120577901.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.