Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

connect two tables with some common string

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 some...
Need Help
Show and Tell
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

rocky09's avatar
rocky09
Solution Sage
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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