Forum Discussion

luc's avatar
luc
Frequent Visitor
8 years ago
Solved

Select rows not present in related table using a measure

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 ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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