- Ciao, friends.
In this video, I want to show you
how to use DAX to change the way
you consider multiple selections in a slicer.
Indeed, by default, multiple selection in a slicer
are using an OR logic.
I want to change that to an AND logic.
Actually, this is much easier to understand
if we go straight to the demo
and we look at the scenario, so let's do that.
I have this report
where I'm showing slicing by year,
the sales amount and the number of customers.
Number of customers is nothing but a DISTINCTCOUNT
of the customer key.
And in this slicer,
I'm selecting both computers, and TV and video,
so I have multiple selections in the slicer.
What do I mean by the fact
that this is using an OR condition?
The number here, sales amount,
is the sales of both computers,
and TV and video together.
So the condition is that the product need to be
either computer or TV and video.
And the same for the number of customers.
These are the customers that bought either computers
or TV and video.
What I want to do is compute a different calculation,
the customers all categories,
which is counting the customers
who bought both computers
and TV and video.
So I want to use the multiple selection on the product
to compute the customers
that bought both products
and if I select three, four, five categories,
then I will find all the customers
that put all the customers at the same time
or in the same period.
To do that, I need to write code
and I already have the measure here
but I'm not interested
in just showing you the code,
I want to build it together.
So let's kill the measure
and start reasoning on top of the calculation.
What I want to do is find the customers
that bought all the selected categories.
So in order to do that,
I will need to find out how many categories
have been selected through the slicer.
Then I will scan the customers
and for each customer, count the number of categories
that he bought in the current selection.
If the two numbers are identical,
that means I found one of those customers.
Now, let's start writing some pieces of code.
New measure and the new measure is the number
of customers with all categories.
As I said, the first step is finding out
how many categories have been selected in the slicer.
So let's save it in a variable.
The number of selected categories,
and that is just a COUNTROWS
of the VALUES of product category.
We probably need to reduce the font a bit.
Okay, let's start by returning this
because we want to check step by step
that the numbers we're computing actually make some sense.
So I'm now just returning number of customers selected,
number of categories selected
and this should return to everywhere.
And we have our first number, two.
The next step is that I want
to find for each customer how many categories
that customer bought
and this is kind of tricky
because if we look at the diagram,
customer is one side of sales,
product is on the other side.
And I can scan the sales table.
Sales contains the customer key
so iterating over customer key gives me all the customers
that bought something in the given period
but then sales contains the product key
and what I want is not only the product key
I want the category that happens
to be in the product table.
So I cannot use only sales
to retrieve the number of categories
that were bought from a customer.
But what I can do is create a temporary table
that contains the customer key
and the category coming from the product table.
SUMMARIZE is going to help me with that.
So what I need to do is create a new variable,
which I call customers and categories.
I use SUMMARIZE.
I SUMMARIZE sales
by sales customer key.
And actually I can put everything on a single row
that will make the code a bit shorter.
By customer key and by product category.
This generates a table
that contains the customer key and the category
for all the pairs that appears in sales.
Let's return the COUNTROWS of customers and categories
just to see a number.
This number will be larger than the number of customers
because if a customer bought only one category,
it will appear once
but if the same customer bought...
multiple categories, then it will appear twice,
twice, three times, four times,
depending on how many times.
So it's not useful but it's useful only to see
that the code is working and that we have some number.
The next step is that I want to transform this table
that contains one row for each pair
of customer and category in a different table
that contains the customer key and the number of categories.
And here I'm in trouble
because I can no longer use SUMMARIZE.
SUMMARIZE works fine when I work with tables
in the model but what I want to do here
is work with a variable.
And the fact columns containing the variable
no longer have the correct lineage,
so it will be impossible to use SUMMARIZE.
But I can use GROUPBY.
I can create a new variable that computes
customers with the number of categories
where I use the GROUPBY function.
So I start from this variable.
I group it by sales customer key.
And I create a new column
that I call categories.
I would like to do a COUNTROWS of category
but again, I'm using GROUPBY.
So GROUPBY is limited in the number of features it has.
I can use either SUMX or COUNTX
over the CURRENTGROUP.
So I will use SUMX over CURRENTGROUP of one.
That actually counts the number of...
the number of categories for each individual customer.
Now, this table now should return the same value
as the number of customers
because I changed the granularity
from customer and category
to only customer where I replaced the category
with the count of categories.
So I would expect this to return the same number
as the number of customers,
which is exactly what is happening here.
So you see that returning the temporary results helps me
in understanding that I'm going in the right direction.
The last step is that out of this table,
I need to remove all the rows
where the count is less than the number
of selected categories
or I can do that in a different way.
I can filter out of this table only the rows
where the number of categories
is identical to the number of selected categories.
So I can create a third variable,
which is call customers with all categories
where I FILTER
this step, no, this step.
Where number of categories
is identical to the number of the selected categories.
And finally, I retrieve the COUNTROWS
of this variable and that should be the result
that I was hoping for.
If I hit Enter,
I now have a different number,
which is the number of customers
that bought all the categories.
So the problem is solved right now
but we can go one step further
because if we pay attention to details,
we can write code
that despite being probably a little slower
can be better in different environments.
Look at that.
What I'm doing here is, I'm retrieving
the sales customer key and the product category.
And finally, I'm using COUNTROWS
to count the number of rows that I retrieved
but I start from sales customer key.
The code as it is now works
to compute the number of customers
but what if I want to apply the same logic
that is filtering only customers
that bought all the categories
to many different measures?
In such a scenario, what I would like to do
is use a calculation item
that applies the filter to whatever measure I put
in the report.
The problem is the measure as I wrote now
would not work because a calculation item
would require some sort of filter applied
to the filter context.
What happens if I change this,
if I try to use my customer with all categories
as a filter?
I can rewrite this as CALCULATE number of customers,
which is, no, COUNTROWS of customer.
And I use as a filter customers with all categories,.
As it is now, it is not,
I would not expect it to work.
If I hit Enter, you see I have the grand total
of all customers everywhere.
And the reason is the filter that I am applying,
customers with all categories contains sales customer key.
So the filter works as long as I stay in the sales table
but if I want to move it somewhere else,
I will not be able to do that.
But I can easily change that
and instead of using sales customer key,
I can group by customer customer key.
Same value, same everything but different lineage.
It's a bit slower
because now this SUMMARIZE requires
to follow the relationship between sales and customer
that was not required earlier
but at the same time, it's more flexible.
I need to change it here.
And now my table contain customer customer key
instead of containing sales customer key.
And therefore, this filter, customers with all categories,
will work on the customer table
and I will be able to count the number
of customers using COUNTROWS.
The number is the same as before.
The overall logic is nearly identical.
The only change was in the way I used my SUMMARIZe.
Again remember, this version is a bit slower
than the previous one.
So it's worth using only
if you plan to use them with calculation items
or in different environments.
The good thing that now this piece of code
does a CALCULATE of something,
this COUNTROWS of customer,
can be as well my number of customers measure
with customers with all categories.
And this will work with any measure.
So next step would be taking this code,
move it to a calculation item
and obtain a calculation item
that changes the logic of a slicer
for whatever measure you put in the report.
Now, let's draw some conclusion.
As you have seen, using DAX,
you can change the way you read the content of a slicer
and use it inside your code.
We use it to move from an OR logic to an AND logic
but in a similar way, you can compute different values,
changing the way you interpret the selection of the slicer.
If you are interested in this kind of topic,
make sure to go to sqlbi.com.
There you will find the article
and you have the option of downloading the file
in order to play with it and learn it better.
Enjoy DAX.