- Ciao, friends.
In this video, I want to analyze with you different ways
of moving a filter from one table to another table.
Whenever you want to apply a filter
that starts from one table to a different table,
there are multiple ways of obtaining the same goal.
A relationship, INTERSECT, TREATAS, CONTAINS.
We will see all of them and as it often happens,
because we have multiple choices,
we will analyze performance
in order to be able to make the right choice
when you need to implement it in your model.
Let's get started.
The first thing is understanding
what it means moving a filter
from one table to another table.
Even though it looks like a rather technical topic,
it is actually happening all the times
when you build your report.
Let's take a look at that.
Here I am slicing by brand
and I'm showing the value of the sales amount.
Brand is in the product table,
sales amount is in the sales table.
So in order to compute the value,
the engine needs to move the filter
that is coming from here
from the product table,
and move the filter from product
to sales detail in order to compute the value.
So moving a filter is something
that happens all the time in your reports.
And the usual method of moving a filter
is to use a relationship as we are doing here.
So the filter from product goes to sales detail.
Sometimes, you don't have the option of using a relationship
and therefore, you need to use DAX
in order to move a filter.
Let's see that with an example.
I have a promotion table here.
Now, the promotion table contains,
let's zoom in a bit, the promotion code,
the name, the discount and the promotion
is applied in a given year to a category of product.
So it's filtering a year from date
and the category from product.
And it would be great to be able
to build relationships between these tables.
So from promotion to date
and then from promotion to product.
But if I do that, or if I were able to do that,
I would create an ambiguous model
because the filter from promotion
to sales detail has multiple parts.
One goes from promotion to date,
sales header and finally to sales detail
and the other one goes from promotion to product
to sales detail.
Therefore, I cannot create the relationships
the way I'd like.
And whenever you cannot create a physical relationship,
you revert to what we call virtual relationships.
A virtual relationship
is nothing but a piece of DAX code
that moves the filter in a CALCULATE statement
in order to obtain the same behavior
that you would have obtained
by creating a physical relationship.
Of course, speed is no longer so cool
but if you need it, it's a technique
that is worth investigating.
Let's see how you can do that.
We leave for, well, we basically leave now Power BI
because we are going to write all the code
just using DAX Studio.
So I just open DAX Studio.
Let's go there.
And we start writing a simple query
that does EVALUATE,
SUMMARIZECOLUMNS,
promotion, let's take the promotion.
And then sales, which is just the sales amount.
Now, if I run the query right now,
you see that the result
is the same number everywhere.
I will need a lot more space.
It's the same number everywhere
because we are missing the relationship.
So if we want to make the relationship work,
we need to move the filter
from promotion to the sales table.
Let's do that by defining a new measure.
Let me define a new measure, sales,
and we call this promotion sales.
The first version will use TREATAS.
As I said, what we need to do
is take the selection on the promotion table
and create a table that filters the sales table.
Now, in order to filter the sales table,
we have basically two ways.
We can go from promotion to date
and then from promotion to product,
relying on the physical relationship
that then will move the filter from date
to sales detail and from product to sales detail.
Or we can go straight from promotion to sales detail
if in sales detail we have suitable columns
that work for us.
For now, we just go from promotion to date
and from promotion to product
because we are not interested in performance at all.
So what I need to do is use CALCULATE.
Compute the sales amount
and here, I need to build a table
that contains the values of the promotion year
and the promotion category.
So I can use SUMMARIZE promotion
by promotion, promotion category
and promotion year.
And here, instead of using sales amount,
we use promotion sales TREATAS.
And let's also provide the right name.
If I run the query now, I get an error.
Why that?
Cannot find, oh, yeah, yeah, it's not
sales because that is sales detail.
Sales detail.
Okay, if I run the query, right now nothing changed.
I have the very same value.
Why that?
Well, the reason is this SUMMARIZE returns columns
in the promotion table.
So the result is a table
that is able to filter this area of the data model.
I need to move the filter from here to date
and to product.
So to do that, I'm going to change the lineage
of my table and say well,
the year is actually a date year
and the category is actually a product category.
To obtain that, I need to use TREATAS
to change the lineage of my table
and say the first column is a product category.
And oh, no way.
The second column is a date
calendar year number.
Now, with TREATAS, the filter will go
from promotion to date and product
and finally, to sales.
If I run the query now,
you see that I have a correct value,
which is the amount of sales
in the years where the category
was actually in the promotion.
So this is a first way of moving the filter.
There are other ways of obtaining the same goal.
One is by using INTERSECT
and the other one is by using CONTAINS.
Let's start by looking at INTERSECT.
We can write, yes, a new measure.
Let's use create a new measure.
A lot of the code will be the same,
so it's worth copying it.
But this time, we use INTERSECT.
INTERSECT, again, we use it
to change the lineage.
We compute sales amount.
We still take the promotion category and the promotion year.
So this is the table containing the category and the year
but we intersect it with a table
that we create on the fly with the correct lineage.
So we are going to use INTERSECT.
And we create the CROSSJOIN
of ALL product category.
This returns all the product categories
with the lineage of the product category.
And ALL date calendar year number.
Now, this part of the query is returning the full CROSSJOIN
of category and year numbers
with the lineage of product category
and calendar year number.
It has all the possible combinations.
But then I'm intersecting it with the combinations
which are visible in the promotion table.
So the results are only the values
that appear in both tables.
Therefore, it basically contains the same result
as this SUMMARIZE but with the correct lineage.
Once I apply that with CALCULATE to the model,
it computes the value.
That is promo sales INTERSECT.
So let's return that in the query.
INTERSECT.
And if I did everything correctly,
now you see that I have the same result here and here.
They are two different techniques.
They obtain the very same result,
even though performance-wise, there is a big difference
but we will talk about performance later.
There is a third technique that is worth learning,
which is using CONTAINS.
CONTAINS uses the same methodology as INTERSECT,
it's just a different syntax.
To use CONTAINS,
we will iterate the table containing the CROSSJOIN
of years and categories
and for each of the pairs, we check if it appears
in the currently visible promotion table.
Let's see that.
Again we copy the measure
because a lot of the code will be very similar.
And that we call CONTAINS.
So we are no longer going to use INTERSECT.
Instead, we are using FILTER.
FILTER will iterate the CROSSJOIN of category
and year number and for each of these pairs,
it checks if CONTAINS,
if the promotion table contains a row
where the promotion category
is equal to the product category,
the currently iterated value
of the product category.
And the promotion year is equal
to the date calendar year number.
This CONTAINS basically checks
if the promotion table contains,
if, yes, the promotion table contains
the currently iterated category and year number.
It returns either true or false
and then FILTER removes all the rows
that do not appear in the promotion table.
This is CONTAINS, no, we do that here.
We add a third row.
This time, it's using CONTAINS.
And again, if I did everything correctly,
you see that I have the same number here, here and here.
There are no differences at all.
Three different techniques
to obtain the same goal.
Now, so far, we used the promotion table
that is really tiny.
It contains a few tens of rows.
So performance is never a problem.
If we were to evaluate the performance,
you will see that it's extremely fast.
I need to expand that again.
Let's enable server timings.
And then we run the query.
Let's look at server timings.
The full query evaluating the three measures
is running in 93 milliseconds.
So it is so fast
that it is nearly impossible to evaluate the difference
between the three different techniques.
But there is, indeed, a very big difference
between the three different techniques
and in order to appreciate the difference,
we are going to use the same model
but a different table
from where to start moving the filter.
Let's start by looking at the model.
Moving from promotion to details,
as I said there are too few rows.
That is the reason why we have the sales header
and the sales detail table.
This is not a best practice
having a header, detail table
but I used it in this model
because I wanted to have a bad model useful
to evaluate performance.
Indeed, the sales header table contains
around 1.6 million rows.
And the sales detail table contains around 12 million rows.
So the cardinality of the relationship
between sales header and sales detail here
is around 1.6 million rows, which is quite large.
And I will write measures
that move the filter from header to detail,
using the relationship,
which is the fastest and the easiest way
to move a filter from one table to another
and then the three different techniques we have seen so far.
In order to do that, I need to disable this relationship
because I won't enable it on demand
and I want to keep it disabled,
otherwise the engine will use the relationship
instead of using my code and that's not what I want.
I want to write the code by hand.
Okay, let's go back to DAX Studio.
This time, I'm not going to write the full code
because it would take forever to write it.
I already have everything ready here.
So let's just quickly look at it.
The first MEASURE sales header amount RELATIONSHIP
computes the sales amount moving the filter
from sales header to sales detail,
using the relationship.
USERELATIONSHIP reactivates the relationship
that I deactivated a few seconds ago.
Amount TREATAS uses TREATAS
to move the filter.
Amount INTERSECT uses INTERSECT
and amount CONTAINS uses the CONTAINS technique.
So I have the three different techniques
plus the relationship.
And then my SUMMARIZECOLUMNS slices by calendar year
and it's worth to note
that calendar year is here,
so the filter starts from date
and it will reach detail through header.
So date filters header
and then from header, I move the filter to detail,
using different techniques.
Let's now run the queries one by one
and let's see the result.
First, let's look at amount RELATIONSHIP.
We already have the server timing here.
I can just run the query.
And it takes 31 milliseconds
to move a relationship that is 1.6 million rows
against a table that is 12 million rows.
So it's pretty fast
and as I told you, it's the best way to obtain this goal.
What about using TREATAS?
We comment that and we remove the comment from here.
If I run the query using TREATAS,
as you are going to see, performance are very different.
Several orders of magnitude of difference.
It takes 5.5 seconds.
So we move from a bunch of milliseconds
to 5,000 milliseconds.
There is a huge difference.
Moving a filter with DAX is way slower
than moving a filter using a relationship.
That is why creating header, detail tables
or creating models
where you need to move the relationship by DAX
is not a best practice
because performance wise, you have an issue.
But that was TREATAS.
What about the other ones?
Let's look at INTERSECT.
INTERSECT is using the intersection,
so it's using a different functionality.
It's no longer TREATAS.
TREATAS was 5.5 seconds.
This is already running since 10 seconds
and it's gonna take 12.8 seconds.
13 seconds.
So TREATAS is much slower than a relationship
but it is faster than using INTERSECT.
What about CONTAINS?
I honestly don't remember
but I would guess that CONTAINS is the slowest one.
Let's see that.
We have INTERSECT, 13 seconds.
Here we are already seven, eight.
No, it's actually not that bad.
It's in the middle between INTERSECT and TREATAS.
So it's 7.9 seconds.
With that said, there is still a huge difference.
When you need to move a filter
between two tables, using a physical relationship
is always the best way.
You revert to DAX only if you really need
and if that is the case, of course
you need to choose the right way.
So as you have seen,
you have multiple ways of moving a filter.
The best, a physical relationship.
Second, TREATAS.
TREATAS is quite well optimized.
It's way slower than a physical relationship
but in some scenario might be acceptable.
And an important detail,
if your table is small, you don't have to worry at all
because at that point,
performance will always be good, no matter what.
You worry about performance
when the table gets larger and larger.
So TREATAS, quite slow.
CONTAINS was not that bad but slower than TREATAS.
And INTERSECT, the slowest one.
With all that said, beware that
your experience might be very different.
Whenever it's time to evaluate performance
of DAX measures, you need to do them on your model.
So the real takeaway of this entire video
is the technique used to evaluate different measures,
having different options, you evaluate them all
and then you choose,
depending on which one is the best
in your specific model.
Enjoy DAX.