Forum Discussion

RichardJ's avatar
RichardJ
Responsive Resident
6 years ago
Solved

Matching a string to a partial string via a relationship

Hi,

Please could anyone advise if what I'm asking is technically possible in PowerBI, and the best approach to meet the requirement.

 

There are two tables:

1) Software Product which contains the field 'Product Name'

2) Software Order which contains the field 'Products'

 

I've created a relationship which matches the Products to the Product Name as shown.

 

ProductsProduct Name
Product 7,Product 1 
Product 1,Product 7,Product 8,Product 1,Product 6 
Product 1,Product 7,Product 2 
Product 1,Product 2 
Product 1Product 1
Product 2,Product 7,Product 8 
Product 2,Product 1,Product 8 
Product 2Product 2
Product 3Product 3
Product 4,Product 5,Product 6 
Product 4Product 4
Product 5,Product 4,Product 3,Product 1 
Product 5,Product 4 
Product 5,Product 6,Product 7,Product 3,Product 1 
Product 2,Product 5,Product 3,Product 4 
Product 5,Product 6 
Product 5Product 5
Product 6,Product 5,Product 1,Product 4 
Product 6Product 6

 

When the fields are shown in a table within a report. As expected, only identical matches in both columns return a value in the 'Product Name' column.

 

My question is to ask if it's possible to create a relationship from the Software Product[Product Name] field to the Software Orders[Products] field where the Product Name is found within the Products.

 

That is, I would like to be able to click on a Product Name and filter which Orders that Product was contained within.

I'd prefer to do this via the creation of a relationship if possible.

 

Hope the question makes sense and thank you for any assistance.

 

Richard

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi, I think the best way to solve this is to create an additional table in the model.

     

    SoftwareOrderProducts

     

    OrderIDProduct
    1Product 7
    1Product 7
    2Product 1
    2Product 7
    2Product 8
    2Product 6

     

    Then you create relationships Order - SoftwareOrderProducts and SoftwareOrderProducts - ProductName.

     

    The comma separated string of products coud be transformed to rows in Power Query.

     

    Best Regards / Ulf

4 Replies