Forum Discussion
Power BI Reverse Slicer (Lookup Returning Multiple Values)
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
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)
3 Replies
Fowmy Thank you so much. It works perfectly!
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)Here is the link to the PBIX file:
https://www.dropbox.com/s/mi35996s75lm1ty/Product%20Category%20%26%20Product%20Name.pbix?dl=0