Exclude values from a table dynamically based on a attribute via a slicer

[Anonymous]

Hi,

let's say I have this sales table:

CustomerID	ProductID	ProductName
1	5	Shirt
1	6	Pants
2	5	Shirt
2	8	Shoes

In my table visual, I only want to select the Customer ID. Without any filters it looks like this:

CustomerId

1

2

Now I want to select all customers who bought shirts(5), but exclude them, if they bought pants(6). So in the end only customerId 2 should be displayed, because he bought a shirt and no pants. How would I do that dynamically via a slicer?

Thanks!

[PaulDBrown · Accepted solution]

@Anonymous 

Here is one way.
Firstly, create two Dimension tables for Product name. Name one"Select Product" and link it to the Data table in a one-to-many relationship. Name the other "Exclude Product", and leave this table disconnected.

The model looks like this:

Next create a measure to show the IDs which include the selected products but exclude the products you wish:

IDs Selected = 
VAR products = VALUES('DataTable'[CustomerID]) //Creates a table of values of IDs with selected products
VAR ExcProducts = CALCULATETABLE(VALUES('DataTable'[CustomerID]), 
                   REMOVEFILTERS('Select Product'[ProductName]),
                    TREATAS(VALUES('Exclude Product'[ExclProduct]), 'DataTable'[ProductName]))
                    //Creates a table of values of IDs with products you wish to exclude
RETURN
IF(ISFILTERED('Exclude Product'[ExclProduct]), 
COUNTROWS(
    EXCEPT(products, ExcProducts)), //Creates a table of IDs which include products selected but not products excluded
    COUNTROWS(VALUES('DataTable'[CustomerID]))) //Returns a count of IDs selected if no product is excluded

Create another measure to filter the "Excluded product" slicer to only show products which are not selected in the "Select Product" Slicer and add this to the Filters on this visual in the filter pane setting the value to 1:

Filter Exclude  table = 
VAR Prods = VALUES('DataTable'[ProductName])
VAR _Exclude = VALUES('Exclude Product'[ExclProduct])
RETURN
COUNTROWS(
    EXCEPT(_Exclude, Prods))

And you will get this:

 I've attached the sample PBIX file for you

[Fowmy]

@Anonymous 

Are you going to enter the product IDs in the formula or have you got them in two separate lists in tables?

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn 

[Fowmy]

@Anonymous 

Add the following measure to your table visual:

Cust M = 

  IF( 5 IN VALUES(Table13[ProductID]) &&  NOT 6 IN VALUES(Table13[ProductID])  , 1 , 0)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

Hi,

this would work for these two IDs, but not if I want to select/exclude multuple productIds. Is there anyway to make this dynamic?