filter a table using another table

[bekirsalgin]

Hello,

I want to discard all Table1 rows that contains no "FamilyName, FirstName" matching with Table2. As long as at least one of the author names is available in Table2, the row should be kept. I guess the soultion should include "data structuring" and creating relationship between the tables but I couldn't find out how to do yet. 

Could you please advise?

[v-yulgu-msft · Accepted solution]

Hi @bekirsalgin ,

Add below measure to visual level filter and set its value to be greater than 0.

Measure1 =
VAR tempTable =
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS ( Table1, "Author", Table1[Author] ),
            SELECTCOLUMNS (
                Table2,
                "FamilyName,FirstName", Table2[FamilyName] & "," & Table2[FirstName]
            )
        ),
        "check", IFERROR ( FIND ( [FamilyName,FirstName], [Author] ), 0 )
    )
RETURN
    SUMX ( tempTable, [check] )

Best regards,

Yuliana Gu