Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.
 
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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