connect two tables with some common string

[Anonymous]

Hi all,

I have two tables in power bi:

1: With Projects Names

2: With URL (which includes project names) 

For the 2 table there are many different URL's with same project name.

I want to somehow connect these two table so that I can use table 1 as a visual filter(drop down).

For ex. 

[rocky09]

Please provide sample data of the two tables.

[Anonymous]

Please refer to the image attached.

[rocky09]

@Anonymous

you have mentioned in your post that you have two tables? I want to see both tables. So, i can provide a solution. Without your data structure, it is hard to guess.

[Anonymous]

Hi  @rocky09,

I have two tables, one has URL's and the other has some project names which are present in the URL, and there is nothing else in thse two tables which is relevant.

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

To achieve your requirement, you can use SEARCH() function to match two tables. Please refer to following calculated column with DAX expression:

Key Column =
CALCULATE (
    IF (
        COUNTROWS ( VALUES ( 'Project Names'[Project Names] ) ) > 1,
        "More than 1 Project Names",
        VALUES ( 'Project Names'[Project Names] )
    ),
    FILTER (
        ALL ( 'Project Names'[Project Names] ),
        SEARCH ( 'Project Names'[Project Names], 'URL'[URL], 1, 0 ) <> 0
    )
)

Thanks,
Xi Jin.

[Anonymous]

Hi @v-xjiin-msft, Thanks for the solution man. It works great.