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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Veternus
Frequent Visitor

Analysis - Multiple Match

Hello guys! 

 

I feel like this should have some obvious solution, however I'm not seeing it and o I decided to ask you for help. 

 

I need to do an analysis of questions asked by our customers about our products. We track contents of the conversations and have output table looking like this: 

IDDateDepartmentProductQuestion
101.01.2024Dpt1"Product A", "Product B", "Product C""Question A", "Question B"
201.01.2024Dpt2"Product A", "Product B""Question A", "Question B"
302.01.2024Dpt2"Product A""Question A"
403.01.2024Dpt1"Product A""Question C"
503.01.2024Dpt3"Product B", "Product C""Question A", "Question B", "Question C"

 

What I want to achieve is, among other things, a graph, that would be showing numbers of asked question, but visually divided (legend) by type of product. Something like this: 

 

Veternus_0-1712659610585.png

 

But I'm struggling to find a way of connecting multiple products of one row to multiple questions. I've tried calculations with SEARCH function, but that doesn't work as some of the product names are just widened names of other products (Product A and Product AB). Also there is 20+ question categories and 20+ products, so something like creating a measure for every combination is something that I would like to avoid. 
My first impulse was to create two more tables, that look like this: 

IDQuestion
1Question A
1Question B
2Question A
2Question C
3Question A

 

IDProduct
1Product A
1Product B
1Product C
2Product A
2Product B

 

But that didn't get me far as the relation between them would be M:M. Is there something I'm missing about connecting them through the main table? Or would you take completely different approach? 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @Veternus 
You need to make some "ETL" to prepare the table for analysis with PQ.

In the first step, clean unnecessary quotation marks at the columns of products and answers :

Ritaf1983_0-1712717176716.png

in the next step split these columns into rows :

Ritaf1983_3-1712717535193.png

 

Ritaf1983_1-1712717338517.png

Then you will get a clean vertical table that you can work with and create desired and other visuals:

Ritaf1983_2-1712717409402.png

The pbix is attached with all the steps of PQ recorded so you can follow them

 

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

 

View solution in original post

2 REPLIES 2
Veternus
Frequent Visitor

Thank you for your insight

Ritaf1983
Super User
Super User

Hi @Veternus 
You need to make some "ETL" to prepare the table for analysis with PQ.

In the first step, clean unnecessary quotation marks at the columns of products and answers :

Ritaf1983_0-1712717176716.png

in the next step split these columns into rows :

Ritaf1983_3-1712717535193.png

 

Ritaf1983_1-1712717338517.png

Then you will get a clean vertical table that you can work with and create desired and other visuals:

Ritaf1983_2-1712717409402.png

The pbix is attached with all the steps of PQ recorded so you can follow them

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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