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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Please refer to the image attached.

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.