Negation of Slicer

[afaro]

I want to have two visuals. One which shows me the total sales for the products selected in slicer A for each customer. 

Then I want to be able to drill through by customer and see all the products purchased by that customer which were not selected in A but part of slicer A. 

[v-jingzhan-msft · Accepted solution]

Hi @afaro 

I have a solution. Here are steps for your reference. The sample file is also attached at bottom. 

First you can have a model like this. The 'Product' table provides a [Product] column in the slicer for users to select on the main page, and it is connected to the 'Sales' table.

Prepare an additional 'Product 2' table which is not connected to 'Sales' table. This table will be used to display products info on the drillthrough destination page. As it is not connected, it will not be affected by the drillthrough filter. 

Then create the following measure. Use it as a filter field in the visual which is to display unselected product info on the drillthrough destination page. Set its value to 1. (In current measure, 0 represents selected and 1 represents unselected )

Measure = IF(SELECTEDVALUE('Product 2'[Product]) IN ALLSELECTED('Product'[Product]), 0, 1)

If you want to display other information in the same visual, you can create measures to get the results and use TREATAS function to pass the filter between two disconnected tables. Just like the [Sales] measure in my sample. 

Result:

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[afaro]

This is genius! Thanks! I was still a bit stumped about how the measure works. 

My understanding of SELECTEDVALUE was that it takes the value coming from a slicer. I was also under the impression that it doesn't work for more than one selected value. In this case, it seems to work a bit differently. I was hoping you could explain that. 

@v-jingzhan-msft 

[v-jingzhan-msft]

Hi @afaro 

Yes, SELECTEDVALUE doesn't work for more than one selected value. However in this case, it doesn't take a value from a slicer. Here I use SELECTEDVALUE ('Product 2'[Product]), and 'Product 2'[Product] is added to a table visual on the drillthrough page. In this table visual, on every row, there is only one 'Product 2'[Product] value, so SELECTEDVALUE takes the distinct Product value of that row. 

Meanwhile, ALLSELECTED('Product'[Product]) is to take the selected values of the slicer from the main page. 

Hope this explanation will make it clear.