cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anthony_dobson
Frequent Visitor

Relating LIKE operation

 

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 NameRelease StatusPackage NameProduct Inventory Lookup String
ArcGIS Desktop 10.7.11595Not PassedArcGIS Desktop 10.7.11595ArcGIS Desktop
ArcGIS Desktop 10.8.14362 Not PassedArcGIS Desktop 10.8.14362 ArcGIS Desktop 
BISNot PassedPackage 12Package_12
GIMSReleasedPackage 12Package_12

 

 

SoftwareInventory Table:

 

PublisherProduct
ArcGISArcGIS Desktop 10.7.11595
ArcGISArcGIS Desktop 10.8.14362 
Dept 1Package 12 R2
Dept 1Package 12 R1
Dept 2ArcGIS Desktop R1

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
anthony_dobson
Frequent Visitor

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
Super User IV
Super User IV

@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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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"

@anthony_dobson , try like

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors