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

A Post From Oceanside!

Yeah, 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:

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

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:

Dissecting the Formula: COUNTX

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?”

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:

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.

Share this:

This entry was posted on Thursday, April 3rd, 2014 at 10:58 am and is filed under DAX, Quick Tips & Tricks, Slicers. You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.

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?

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..