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
Anonymous
Not applicable

Check if Row in Table Exists in Another Inactive Related Table

I have a table called 'Filtered Items' which is a duplicate of a table called 'Items'.

 

I've created a slicer containing 'Filtered Items'[Name] and a table card containing 'Items'[Name].

 

The behavior I am trying to create is to remove any items in the table containing 'Items'[Name] where 'Items'[Name] can be found in the selected values of the slicer containing 'Filtered Items'[Name].

 

Here is what I've tried so far but is not working:

 

COUNTROWS(
CALCULATETABLE(
ALLSELECTED('Filtered Items'),
USERELATIONSHIP(Items[ILC], 'Filtered Items'[ILC])
)
) > 0
I was hoping that in the table visualization each row of Items[Name] will filter 'Filtered Items'[Name] and return 0 rows when Items[Name] cannot be found in the selected slicer values.
 
1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous

 

I'm not sure I completely understand what you are aiming for but let's try this:

 

1. Place a matrix visual in your report with 'Items'[Name] in rows

2. Create a measure to determine what items will be shown:

 

ShowMeasure =
IF (
    NOT ( SELECTEDVALUE ( 'Items'[Name] ) IN VALUES ( 'Filtered Items'[Name] ) ),
    1
)

 

3. Place the measure in the visual level filters of the matrix visual and select to 'Show items when the value:'--> is 1

 

By doing that, you will filter out the names that are not selected in the slicer

View solution in original post

1 REPLY 1
AlB
Super User
Super User

Hi @Anonymous

 

I'm not sure I completely understand what you are aiming for but let's try this:

 

1. Place a matrix visual in your report with 'Items'[Name] in rows

2. Create a measure to determine what items will be shown:

 

ShowMeasure =
IF (
    NOT ( SELECTEDVALUE ( 'Items'[Name] ) IN VALUES ( 'Filtered Items'[Name] ) ),
    1
)

 

3. Place the measure in the visual level filters of the matrix visual and select to 'Show items when the value:'--> is 1

 

By doing that, you will filter out the names that are not selected in the slicer

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.