Filtering a table using values in a separate table and wild character

[aabati]

Dear community,

I need your help.

I have a Table_A containing 2 columns:

- ID

- Description

I have a Table_B containing one column:

- Lookup

And I'd like to filter the rows in Table_A using the following rule: select * from Table_A where description "contains" any value in the column Lookup.

Let me explain better with an example:

Table_A

1     Corporate|House

2     House

3     Car|Corporate|Bike

4     Van

Table_B

Corporate

Van

I want to return the rows 1,3 and 4 becasue they contains one of the values in the Table_B

Many thanks

A.

[Anonymous]

@aabati 

I assumed there is a relationship between the tables, you would need to first split the description column by "|“.  Then create a calculated table using the below function to filtered the matched results.

Table = CALCULATETABLE(TableA,FILTER(TableA,TableA[Discription.1] in VALUES(TableB[Lookup])||TableA[Discription.2] in VALUES(TableB[Lookup])||TableA[Discription.3] in VALUES(TableB[Lookup])))

BTW you could use CONCATENATE function to join them together if needed.

Best,
Paul

[aabati]

Thanks Paul for the replay

I understand you solution but in my case I dont have a defined and limited number of | (pipe delimiters). 

This is why I was looking for a kind of "Select like %' function.

Thanks

A.

[aabati · Accepted solution]

I think I found a solution in this article:

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-ta...

Hope this can be of any help to other users as well

A.

[trebgatte]

You may want to look at this blog post as it is a similar data structure. https://marqueeinsights.com/how-to-work-with-sharepoint-multi-value-columns-in-power-bi/

What you don't have is a cross-reference between Table A and Table B. The post shows you how to easily do this and set up the relationships so that you can filter as described.

Hope this helps!

--Treb, Power BI MVP

Check out our Power BI blog posts at https://marqueeinsights.com/category/power-bi/