Power BI Reverse Slicer (Lookup Returning Multiple Values)

[Anonymous]

Hello,

I would like to build a reverse slicer in Power BI based on the two tables below:

Product Category	Product Name
A	Pencil
A	Pen
A	Ruler
B	Pencil
B	Watch
C	Sticker
C	Monitor
C	Ruler
D	Monitor
D	Keyboard

Product Name

Pencil

Pen

Ruler

Watch

Sticker

Monitor

When filtering (a slicer) on Product Name = Ruler, I only would like to see the result below in a matrix (because only Product Category B & D do not contain Product Name = Ruler):

Product Category

B

D

Similarly, when filtering (a slicer) on Product Name = Pencil,  I only would like to see the result below in a matrix (because only Product Category C & D do not contain Product Name = Pencil):

Product Category

C

D

I tried using Lookupvalue, but it did not work because it would only return a single value. Please help.  Thanks!

[Anonymous]

@Fowmy  Thank you so much. It works perfectly! 

[Fowmy · Accepted solution]

@Anonymous 

Create this measure and assign it to the visual filter of the matrix and set it equal to 1. The file is attached below my signature.

Filter Measure = 

var __product = SELECTEDVALUE('Product Name'[Product Name]) 
var __category =  SELECTEDVALUE('Product Category & Name'[Product Category])
var __prodcat = 
    CALCULATE(
        MAX('Product Category & Name'[Product Category]),
        'Product Category & Name'[Product Name] = __product
    ) <> __category
return
    INT(__prodcat)

[Anonymous]

Here is the link to the PBIX file:

https://www.dropbox.com/s/mi35996s75lm1ty/Product%20Category%20%26%20Product%20Name.pbix?dl=0