Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luc
Frequent Visitor

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 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?

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @luc,

 

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

1.PNG

 

Create measures as below:

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

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

2.PNG3.PNG

 

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.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @luc,

 

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

1.PNG

 

Create measures as below:

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

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

2.PNG3.PNG

 

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.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.