Turning “OR” Slicers Into “AND” Slicers

image

In this Report, We Are Only Seeing Customers Who Have Purchased
Both Accessories AND Clothing During 2004

A Post From Oceanside!

imageYeah, I’m on vacation (my first real vacation in 5+ years), so why am I writing a post?  Well, it’s before 9 am, the family is still sleeping in, and I honestly loved the idea of slipping out to write a post while looking at the ocean. 

The truth is I LOVE writing these posts – in some sense they represent Peak Fun for me, especially when they can be written at a relaxed pace with no outside pressures.  In the future, maybe I will take vacations for the express purpose of writing.  (That sounds surprisingly good to me actually).

Slicers – The More You Select, the More You “Get”


 

First, let’s be absolutely clear what I mean by Slicers using “OR” logic.

Here’s a simple measure:

[Active Customers] =

DISTINCTCOUNT(Sales[CustomerKey])

And here’s what it looks like when we slice it to 2004 and Accessories:

image

9,435 Different Customers Purchased at Least One Product
from the Accessories Category in 2004

image

When I Ctrl-Click Clothing, I Get MORE Customers – Apparently There were 1,320 Customers Who Bought Clothing But Did Not Buy Accessories

So, in order to be “counted” by our [Active Customers] measure, a customer needs to have bought an Accessories OR a Clothing product in 2004. 

That’s how slicers work – the more you select, the more you get.  Because all you have to do is meet ONE of the selected criteria in order to be included.  This is a lot like using the OR() function in Excel or Power Pivot:

IF(OR(customer bought Accessories, customer bought Clothing), include customer, exclude customer)

Changing OR to AND

What if I want the number to go DOWN instead of UP when I add selections?

Let’s write a measure that returns 1 when a customer has bought everything selected, and 0 when they haven’t:

[Bought All Selected Categories] =

IF(COUNTX(ALLSELECTED(Products[Category]),[Order Lines]) =
   COUNTROWS(ALLSELECTED(Products[Category])),
   1,
   0
  )

And the results (it’s easier to visualize this when looking at Customers on rows):

image

A Bunch of Aarons Bought Accessories in 2004:  Aaron Bryant, Aaron Butler, etc.

But then I add Clothing to the selection on the slicer and I get:

image

Only Aaron Collins Bought Both Accessories AND Clothing!

Dissecting the Formula:  COUNTX

Starting with the COUNTROWS section (highlighted in green):

IF(COUNTX(ALLSELECTED(Products[Category]),[Order Lines]) =
   COUNTROWS(ALLSELECTED(Products[Category])),
   1,
   0
  )

ALLSELECTED(Products[Category]) – this is going to be a table of 1-4 rows.  Why?  Because there are only 4 values of the Products[Category] column – Clothing, Bikes, Accessories, and Components.

So if you select Clothing and Accessories on the Slicer, you’re going to get a 2-row table – just the values “Clothing” and “Accessories” – for the ALLSELECTED.  And then COUNTROWS is going to count those rows.

In other words, the green part is just a way to ask “how many Categories are selected on the Slicer?” 

Now let’s look at the yellow part, the COUNTX:

IF(COUNTX(ALLSELECTED(Products[Category]),[Order Lines]) =
   COUNTROWS(ALLSELECTED(Products[Category])),
   1,
   0
  )

The first input to COUNTX is ALLSELECTED(Products[Category]) again – so the COUNTX is going to only “look at” the selected values on the Slicer.

For each row in the ALLSELECETED, COUNTX is going to evaluate the measure [Order Lines], which is just a COUNTROWS of the Sales table – COUNTROWS(Sales).  That’s going to return a number (when there are order lines) or blank (when there are no lines).

And COUNTX counts numbers as 1’s, and blanks as zeroes!  So if a customer bought both selected categories, the COUNTX will return 2.  If they bought only one of them, COUNTX will return 1.

Finally, the IF is just checking to see if those two values match – so let’s “rewrite” the formula as:

IF(Number of Categories Purchased) =
    Number of Categories Selected on Slicer,
   1,
   0
  )

Filtering the Pivot

Now we can just set a Row Labels, Value filter on Customer:

image

 

Now we’re down to just the customers who qualify:

image

 

We can then add a measure like [Total Sales]:

image

Not Displaying Individual Customers

But listing out all those customers might not be what we want.  We’d prefer something like:

image

 

The formula for which is:

[Customers Who Bought All Selected Categories] =

CALCULATE([Active Customers],
          FILTER(Customers,
                 [Bought All Selected Categories]=1
                )
         )

Yes, I could have done this as a COUNTROWS(FILTER(…)), but I like the form above because you can then substitute [Total Sales] or any other measure for [Active Customers], and get the same “AND-sliced” effect.

All right, back to the pool.  See you folks next week.

10 Responses to Turning “OR” Slicers Into “AND” Slicers

  1. Tom says:

    WOW, I am gonna need a whole week sitting poolside to take this all in. Thanks for fitting in a great lesson. Enjoy the relaxation.

  2. Marco Russo says:

    This approach is interesting, because you don’t add tables to the data model. The “classical” solution to this problem is described in “basket analysis” blog posts (such as http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/19/powerpivot-basket-analysis-and-the-hidden-many-to-many.aspx and http://blog.gbrueckl.at/2014/02/applied-basket-analysis-in-power-pivot-using-dax/). In the many-to-many whitepaper (http://www.sqlbi.com/articles/many2many) we included a longer explanation of this scenario.
    I wrote two patterns describing this: the survey pattern (http://www.sqlbi.com/daxpatterns/survey – scheduled to be published on May 26, 2014) and the basket analysis pattern (http://www.sqlbi.com/daxpatterns/basket-analysis, scheduled on June 9, 2014), but they always use an additional table. Being able to solve it in a DAX formula could be useful when you cannot change the model (for example when you published it on Tabular or SharePoint).
    I am somewhat concerned about performance, which depends on the number of Customers you have – but this should be measured. Probably it works well if you don’t have big tables (which might be the case in many PowerPivot models).
    Have a very nice vacation!

  3. Ok I had this item on the the todo list for next week :) looks like you saved me some time :) great post!

  4. George Qiao says:

    Impressive! I prefer this approach when working with small data set as from the UI perspective, it is very straight forward to business user.

  5. Frenk says:

    I like how all great minds come together in the comments section here :)

    Also, sweet solution! I can totally see the use for this at a recent client.

  6. Frenk says:

    What are your thoughts on adding a separate slicer containing only the values AND and OR, which is connected to the Categories slicer, giving the user a choice in how they want the filter te work? Is this something you would consider and if so, how would you implement it?

  7. Kim says:

    Love the approch and the UI for data discovering. Only thing I’m strugeling with implementing together with this is a another slicer for filtering customers who has’nt bought from a certain categori. Can’t figure out how..

Leave a Comment or Question