Lookupvalue using Search\ContainsString?

[smoka3]

Hi Experts, could you please help in finding the solution for my requirement

I have 2 tables with 40K rows in each table,

I have slicer with package column from Table A and i have a table visual from Table B,

no relationship between Table A and Table B due to no common key value, but TableA[package] is a substring of TableB[Command]

my requirement is, when i filter TableA[package] using slicer, the related data should filter in tableB.

how can i solve this?

I tried Calculated table using CONTAINSSTRING Function

I tried Search function and LOOKUPVALUE function 

nothing worked out for me.

here is data looks like

Table A:

Package

getHelp

getSales

migarteData

Table B: (may contain duplicates in command column)

Service	Command
Service1	C:\file\source\getHelp\folder\aaa
Service2	\\filegroup\folder\getSales\
Service3	service.ex migrateddata from 2
Service4	D:\file\source\getHelp\folder\aaa

Expected Output

Service	Command	Package
Service1	C:\file\source\getHelp\folder\aaa	getHelp
Service2	\\filegroup\folder\getSales\	getSales
Service3	service.ex migratedata from 2	migratedata
Service4	D:\file\source\getHelp\folder\aaa	getHelp

[amitchandak · Accepted solution]

@smoka3 , Create a new column in TableB

maxx(filter(TableA, CONTAINSSTRING(TableB[Command], TableA[Package])),TableA[Package])

[smoka3]

Thanks You! 

[Greg_Deckler]

@smoka3 - So basically this is a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534.

Kind of.

In your case, something like this:

Measure =
  VAR __Command = MAX('Table B'[Command])
  VAR __Table = ADDCOLUMNS(ALL('Table A'),"Include",FIND([Package],__Command,,0))
RETURN
  MAXX(FILTER(__Table,[Include]>0),[Package])

Package... LOL...

[smoka3]

Thank you!