Relating LIKE operation

[anthony_dobson]

Basically I am looking of a way of relating certain data in PowerBI. The "Product Inventory Lookup String" is a discovered string subset of the SoftwareInventory table "Product" column. So I want to use this LookUp String as a LIKE on the Product column to match. This means multiple "Product" matches are desired. Obviosuly this can;t be done via relating tables directly. What is DAX or Power Query way to do this?

AppMapping Table:

Suite Name	Release Status	Package Name	Product Inventory Lookup String
ArcGIS Desktop 10.7.11595	Not Passed	ArcGIS Desktop 10.7.11595	ArcGIS Desktop
ArcGIS Desktop 10.8.14362	Not Passed	ArcGIS Desktop 10.8.14362	ArcGIS Desktop
BIS	Not Passed	Package 12	Package_12
GIMS	Released	Package 12	Package_12

SoftwareInventory Table:

Publisher	Product
ArcGIS	ArcGIS Desktop 10.7.11595
ArcGIS	ArcGIS Desktop 10.8.14362
Dept 1	Package 12 R2
Dept 1	Package 12 R1
Dept 2	ArcGIS Desktop R1

[anthony_dobson]

I solved this by making a new column in the SoftwareInventory table similar to @amitchandak first reply and then related this new column to Product Inventory Lookup String from AppMapping table

Thanks!

[amitchandak · Accepted solution]

@anthony_dobson , you can create a new column in AppMapping table

maxx(filter(SoftwareInventory, search( AppMapping[Product Inventory Lookup String], SoftwareInventory[Product],,0)>0),SoftwareInventory[Publisher])

This can slow as there is no equal column

[anthony_dobson]

Hey I am after all matches from Product column. So I want to match Package 12 from "Product Inventory Lookup String" and return multiple rows as additional table rows: "Package 12 R2" + "Package 12 R1"

[amitchandak]

@anthony_dobson , try like

concatenatex(filter(SoftwareInventory, search( AppMapping[Product Inventory Lookup String], SoftwareInventory[Product],,0)>0),SoftwareInventory[Product]," + ")