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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors