How to match value from one table that could be in multiple columns in seperate table?

[OldN00b]

Hi there, I am new to Power Bi and DAX and am having difficulty selecting and displaying records based on a single value that exists in multiple columns in my data set. For background, i am connecting to a sql db which pulls both table 1 and table 2 using directquery.

Table 1 contains unique securityids in one column where table 2 contains securityid's through 5 different columns. I am trying to look up for each securityid in table 1 if i can match in either of the 5 columns in table 2. Table 2 holds my attributes.

These tables currently dont hold a relationship due to the fact that i dont know how to match them if the second table can have securityid's in multiple columns.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new calculated column.

In Table2 CC =
IF (
    Table1[SecurityID]
        IN DISTINCT ( Table2[SecurityID1] )
            || Table1[SecurityID]
                IN DISTINCT ( Table2[SecurityID2] )
                    || Table1[SecurityID]
                        IN DISTINCT ( Table2[SecurityID3] )
                            || Table1[SecurityID]
                                IN DISTINCT ( Table2[SecurityID4] )
                                    || Table1[SecurityID] IN DISTINCT ( Table2[SecurityID5] ),
    "Yes"
)