Forum Discussion

manoj619's avatar
manoj619
Regular Visitor
3 years ago

need to match data from a different table that includes the text of the current table column

I have a sysid and a status column in table 1, and I need to search for and retrieve data from table 2's sysid column that includes the text from table 1's sysid column Expecting output ...
Need Help
AmiraBedh's avatar
AmiraBedh
Super User
3 years ago

Try the following :

FILTER(
Table2,
SEARCH(
MAX(Table1[sysid]),
Table2[sysid],
,
0
) > 0
)

 

The MAX function will get the maximum value of the sysid column in Table1, assuming that each sysid appears only once in Table1. 

The SEARCH function will search for the sysid text in the sysid column of Table2. If the sysid text is found, the function returns value different from 0, which is used to filter Table2 using the FILTER function.

 

Be careful with the SEARCH function since it is case-sensitive, so if you want to perform a case-insensitive search, using FIND can be an alternative.

manoj619's avatar
manoj619
Regular Visitor
3 years ago

Thanks for the reply,

but, i am getting error like this when I tried

 

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    3 years ago

    I tried to reproduce what you provided in the screenshot. You need to create a calculated table like below : 

    Table 2 = FILTER('Second Table', CONTAINS('First Table', 'First Table'[sysid], 'Second Table'[sysid]))

     

    I am attaching the PBI file.

    • manoj619's avatar
      manoj619
      Regular Visitor
      3 years ago

      I tried again, but if there are multiple texts in the column table, it fails.

      findtext.pbix

      • AmiraBedh's avatar
        AmiraBedh
        Super User
        3 years ago

        You are trying to create a calculated column while in the solution I provided it is a calculated table

        Can you provide your expected output ?