Check if Row in Table Exists in Another Inactive Related Table

[Anonymous]

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.

[AlB · Accepted solution]

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