Select rows not present in related table using a measure

[luc]

I have a User table and a second table with a User column related to it, and I want to know which Users don't show up in the User column of the 2nd table. A calculated column with CONTAINS allows me to do this but calculated columns don't work with slicers, so I would like to do this with measures so I can have a visual with a datetime slicer.

CONTAINS doesn't let me compare columns of two different tables when I try to use it with a measure. Is there another way I can achieve this?

[v-yulgu-msft · Accepted solution]

Hi @luc,

Please remove the relationship between User table and second table. Or set the "cross filter direction" to single.

Create measures as below:

Measure1 =
CONCATENATEX ( ALLSELECTED ( 'Second Table' ), 'Second Table'[UserName], "," )

Measure2 =
IF (
    ISERROR ( FIND ( SELECTEDVALUE ( 'User Table'[UserName] ), [Measure1] ) ),
    "Not Exist",
    "Exist"
)

If you only want to display those user names that show up in the User column of the 2nd table, please set the visual level filter like below.

Best regards,

Yuliana Gu