Display Search Value and table name as result from multiple tables

[SantoshKumar]

Hi,

 i have three tables and i want to search for each value from table1 in table2 and table3.

End result should be such that i want to know in which tables does my searchedvalue exists.

Example :

Sample Data: Three tables

Table1	Table2	Table3
DeviceId	DeviceId	DeviceId
InstallDate	TargetOs	TargetOs
Name	IngestDateId	IngestDateId
Publisher	IngestTime	IngestTime
Source	Inf	Source
Version	Version	DriverVerDate

Expected Result:

DeviceId	Table2	Table3
InstallDate
Name
Publisher
Source		Table3
Version	Table2

Can we achieve this output using power bi?

Thanks

Santosh

[MFelix · Accepted solution]

Hi @SantoshKumar ,

You can add the following measure:

Result = var Table_2 = 
 IF(
      SUMX(Table2,
           FIND(
                UPPER(SELECTEDVALUE(Table1[Table1])),
                UPPER(Table2[Table2])
                ,,0
               )
          ) > 0,
      "Table2",
      ""
     )

var Table_3 =  IF(
      SUMX(Table3,
           FIND(
                UPPER(SELECTEDVALUE(Table1[Table1])),
                UPPER(Table3[Table3])
                ,,0
               )
          ) > 0,
      "Table3",
      ""
     )

Return
CONCATENATE(Table_2,Table_3)

If you prefer a colum you can add the following code:

Result_Colum = var Table_2 = 
 IF(
      SUMX(Table2,
           FIND(
                UPPER(Table1[Table1]),
                UPPER(Table2[Table2])
                ,,0
               )
          ) > 0,
      "Table2",
      ""
     )

var Table_3 =  IF(
      SUMX(Table3,
           FIND(
                UPPER(Table1[Table1]),
                UPPER(Table3[Table3])
                ,,0
               )
          ) > 0,
      "Table3",
      ""
     )

Return
CONCATENATE(Table_2,Table_3)

[SantoshKumar]

Hi @MFelix 

Yes, It answered my question and thanks for the Help !!

Thanks

Santosh