There are lots of different ways you can filter data in Power BI including:

• Slicers (with various configurations)
• Cross filtering from any visual object
• The filters pane on the right-hand side of your report

Most of these filters require you to select specific value(s) from a list of values.

For example, we want to select all desserts from a menu of food items. We can do this simply having a course type slicer and a table with food items.

The Problem

What if you are on diet and you want to have all the food items but not desserts? Interesting, right?

One simple answer is selecting all the courses except desserts, but what if a food item falls under multiple courses? Or, what if there are lot of options to select instead of just five course meals?

My friend from the marketing department shared a similar problem with me. The client wanted to target their customers who did not buy a certain product. We needed to build a report that contained select customers who didn’t order a certain product.

The Negative Solution

Remember the Cartesian product, that lost its way in your college books? Well, it is back!

Although, calculating Cartesian product takes a toll on memory when working with many rows, sometimes there is no way around it.

What we have:

1. Product table with unique Product IDs.
2. Customer table with unique Customer IDs.
3. Orders table with Order ID, Customer ID and Product ID.

What we want:

• A table with Customer ID and Product ID.
• Multiple rows per Product with each row having Customer ID who hasn’t bought that product.

 

 Four Simple Steps

1. Cartesian Product
2. Summary Table
3. Join the Cartesian Product with summary table.
4. Filter

 

• Step 1: Create a cross product of PRODUCTS x CUSTOMERS

Product ID	Customer ID
1	C1
1	C2
1	C3
2	C1
2	C2
2	C3
3	C1
3	C2
3	C3

 

• Step 2: Create a summary table from ORDERS Table grouping on Product ID and summarized column with comma separated list of Customers who have bought the product.

Product ID	Customer ID
1	C1,C2
2	C1,C3
3	C2

 

• Step 3: Join the Cartesian product with a summary table containing the Product ID as a common parameter.

Product ID	Customer ID	Customer  ID
1	C1	C1,C2
1	C2	C1,C2
1	C3	C1,C2
2	C1	C1,C3
2	C2	C1,C3
2	C3	C1,C3
3	C1	C2
3	C2	C2
3	C3	C2

 

• Step 4: Filter the resultant table from Step 3 so that all the rows where a Customer ID can be searched is on the corresponding Customer ID list.

Product ID	Customer ID	Customer  ID
1	C3	C1,C2
2	C2	C1,C3
3	C1	C2
3	C3	C2

 

• Resultant table from step 4 has multiple rows per Product with each row having Customer ID who hasn’t bought that product.
• A slicer can be made from the Product ID column to view customers who haven’t bought a product.
• A slicer can be made from the Customer ID column to view products not bought by a customer.

 

Conclusion

Negative filtering technique in Power BI can be helpful for filtering and visualizing unrelated data that can be used in a positive and powerful way.

Read Next: Reasons Why Power BI is Taking Over Tableau