- Ciao, friends.
In this video, I want to show you how to use filters
that use multiple columns.
Let's take a look at the measure I wrote here.
It does CALCULATE sales amount
where product brand equals Contoso
and product color equals green.
Because I used two filters in CALCULATE,
they are placed in a logical AND.
They are intersected.
Therefore, the number that I see here
is the value of sales, which are green and Contoso.
What if I want to do an OR condition?
For whatever reason, I want to see the sales,
which are Contoso or green.
I cannot use multiple conditions in CALCULATE
because by default, they are placed in an AND.
So I might want to use an OR condition.
I can add an OR operator,
but this is not going to work.
If I hit Enter, you see that I obtain an error.
The error says that the expression contains multiple columns
but only a single column can be used
in a True/False expression inside CALCULATE.
What's the meaning of that?
I have used the compact syntax of CALCULATE
that will be transformed into a table
but I'm using two columns,
so the engine does not know
if you need to do ALL brand or ALL color.
Using the compact syntax, I cannot use more than one column
but I can go for the extended syntax
and write a FILTER over ALL product
where product brand equals Contoso
or product color equal green.
This seems to work and indeed, it will work
and produce the expected result here.
But it has a very big problem.
Using ALL product, I'm replacing the filter not only
on the brand or color columns,
I am removing any filter from the product table
and replacing it with a filter over brand and color.
Therefore, it is likely not to work in a different report.
Remember, you always need to work with the minimum number
of columns, which are required for your goal.
In our case, we only need the brand and the color.
So filtering ALL product is just too strong.
We need to build a table
that only contains brand and color
and removes filters from only these two columns.
We have the ALL function that does it.
Instead of doing ALL product,
we can do ALL product brand, product color.
This version of the code is much safer
because we touch two columns
and we use only these two columns.
The result is not going to change here
but this measure, used in different report
will behave much better.
You see that if I use FILTER ALL brand color this way,
I obtain the desired result.
Now, this syntax replaces the filter
on two columns but because I had to use ALL,
it will overwrite, it will ignore outer filters
and replace them with the current filter.
What if the user applies a filter on the column?
For example, applies a filter on gold or green?
You see that the number does not change
because the filter on gold and green
is always replaced by my filter.
If I want to not ignore the filter
but I want to feel the outer filter
and add the filter for Contoso and GREEN
then I can mix a table filter,
a table with two column filters, with KEEPFILTERS.
So I can change my measure
and add KEEPFILTERS around it.
Writing it in this way, now I have different semantics.
This is building a table with all the brands and color,
filtering only Contoso and green,
so all the brands and colors
where the brand is Contoso or the color is green
but this filter will not replace the outer filter.
It will be put side by side with the outer filter.
So the outer filter will be kept
and this will be added as a further filter.
You see that once you learn
that filters of CALCULATE are tables,
you can start to build more complex tables
and always obtain the goal that you need.
Enjoy DAX!