- Ciao friends.
This video is about set manipulation functions in DAX.
A table of values in DAX can be considered
as a set of items.
And we can manipulate tables
using functions called INTERSECT, EXCEPT and UNION,
which represent corresponding set manipulation operations.
So before looking at the DAX code,
let me spend a few minutes
describing these concepts on the whiteboard.
So we have two sets here, A and B,
and I populate A with two weekdays,
Sunday and Monday.
I do the same in B, and I have here,
Monday and Tuesday.
So I have different days,
but actually Monday exists in both A and B.
So now if I move B over A,
I can see that graphically,
there is one element that is common to both sets,
which is Monday, whereas Sunday and Tuesday
only exist in either A or B.
So before moving forward,
let me duplicate this small chart,
because what I want to do, I want to replicate this
in three different copies
because I have three different operations
that I want to describe, which are
the UNION, the INTERSECTION, and the EXCEPTION.
So let's start with this first example.
We have two sets, A and B,
and the first operation I want to describe is the UNION.
So the UNION is also represented like A U B.
And in order to represent this graphically,
you can imagine that the UNION is the sum of the two sets.
So in this case, I can highlight this with a green color,
which shows the two sets together.
So the UNION is the sum of the two sets together.
Now, one detail could be how many unique values we have here
'cause Monday was actually present in both A and B.
And we will see that in DAX,
the UNION has the two copies of Monday,
and not just a single value,
but we will see this in the DAX code later.
Conceptually, the UNION is the combination,
is the sum of the two sets.
Now let's talk about the second operation,
which is INTERSECT.
So the INTERSECT operation is also defined
with this notation.
And basically it means, show me only the values
that exist in both A and B sets.
So in this case, if I want to highlight this,
I can just color the section
that represent the INTERSECTION of the two sets.
I have only Monday for the INTERSECTION.
And now finally, we see the last operation, which is EXCEPT.
So EXCEPT means we want to remove
from one set all the items that exist in a second set.
And this is represented this way.
So in this case, the order is important
because we're saying we want to remove from the A set
whatever exists also in the B set,
which means that
if you look at the graphical representation,
we only want to include Sunday
'cause we want to remove Monday, Monday exists also in B.
And so the result of the EXCEPT operation
is the red color that you see,
the part that is colored here in this set.
So we can see here the three operations,
UNION, INTERSECT, and EXCEPT
that we are going to discuss in the DAX code.
Let's start with the UNION function.
Here, we have DAX code that creates two variables
corresponding to the two sets
we have seen on the white board before.
The first set has Sunday, Monday,
and the second set Monday, Tuesday.
So if I run this query just displaying Sunday, Monday,
I see two days, Sunday and Monday,
and the same for the second set, Monday and Tuesday.
Now here I can manipulate these two variables
using, for example, the UNION function.
UNION can have two or more arguments,
and I can provide Sunday, Monday and Monday, Tuesday.
If you remember the result should be the set of all the days
that are in the two variables.
If I run here, I can see that actually
I have all the elements of the first set
and of the second set together.
It's important to note that we have
two rows with the same day, Monday.
So duplicated values that exist in multiple sets
are combined in the final result,
keeping the duplicated values.
If we want to remove the duplicated values,
we have to embed UNION in a DISTINCT function,
this way.
And DISTINCT when called with a table expression
removes the duplicated values
that return from this table expression.
So let's see what happens when we use INTERSECT.
If I replace UNION with INTERSECT,
I remove the last parenthesis here.
If I run this code, what I should get
is the list of days that exist in both tables,
which is only Monday.
So this is the INTERSECTION between the two sets.
Finally, if I use EXCEPT
I remove from the first argument,
all the values that are in the second argument.
So I remove from the set Sunday, Monday,
the days that exist in Monday, Tuesday.
So the result should be only Sunday.
Now this explains what these functions are
in terms of behavior.
But there are a few more details
about what happens if there are tables
that don't have the same number of columns
or the same data types.
So most details of these differences
are included in the article,
I just want to mention one that is common.
So in this case,
we have two table that have one column each.
But if, for example, I change the definition of this table
and I include here, from date, not just the day of the week,
but for example, the day week number I have here.
So if I apply this change,
you see that if I now write Sunday, Monday
I obtain a table that has two columns.
Whereas the second table Monday, Tuesday
has only one column.
At this point if I try to UNION the two tables,
Sunday, Monday, and Monday, Tuesday, what happens?
I get an error.
So in order to retrieve the result of UNION,
INTERSECT, EXCEPT, the first fundamental requirement,
we need tables that have the same number of columns.
There are also requirements about the data type.
And there could be some automatic conversion,
but, to be honest, I suggest you always use tables
that have the same data type,
so you don't get unexpected result
or in some cases, some error.
Finally, the data lineage of the result of this function
is usually the data lineage of the tables
if the tables have the same data lineage.
So if you get two columns, like in this case,
we used at the beginning VALUES of day of week
in both variables.
In this case, the data lineage is still day of week
because we are getting two tables
that have the same data lineage in all the columns.
However, there is some exception.
INTERSECT is a function that always keeps the data lineage
of the first argument
whereas the other functions
don't provide a valid data lineage
if the data lineage is different in the two arguments.
However, more details about these differences
are included in the article,
and enjoy DAX.
(light funky music)