- Ciao, friends.
With the introduction of composite models,
DAX has been enriched with specific features
that are needed in order to make composite models
over Analysis Services work.
This includes DEFINE TABLE and DEFINE COLUMN.
I speak in another video about the DEFINE COLUMN statement.
The goal of this video
is to talk about the DEFINE TABLE statement,
the capability of creating calculated tables
that exist only for the lifetime of a query.
So a calculated table that behaves the same way
as a calculated table in the model
but it is not persisted in the model,
it exists only for the lifetime of a query.
Columns in this table will have their own lineage.
You can use this table
as if it were a regular table in the model
with the difference that it exists only
for the lifetime of a query.
Calculated tables and queries come
with several limitations so we will need to discuss them
in order to understand when to use it
and when not to use them.
Let's get started.
I have our regular Contoso model here
but in order to use the new DEFINE TABLE statement,
what I need is a query.
Indeed, if you want to create a query calculated table,
you need a query.
That's why the first thing that we do
is run DAX Studio and start writing a query.
Let's leave some space for the code.
Now, let's pretend that we want to create a new table
that contains the brand, the color
and the sales amount.
So it's a new table that we could create
as a calculated table in the model,
but we want to do that inside the query.
We need to start with DEFINE TABLE
and then we need a name, colors brands and sales.
What follows is a table expression
that contains the code, so we can use
a SUMMARIZECOLUMNS by product brand
and by product color.
And we create a new column amount,
which is the sales amount.
Now, this is just a regular table,
a calculated table that I can use.
Being a calculated table, it has its own lineage.
So I can write EVALUATE
and then use SUMMARIZECOLUMNS
and group by my colors brands and sales, colors brands
and sales, brand.
And I can also create a new column,
like total amount, and I can use SUM of.
Now SUM works with columns that belong to the model
but it also works with columns
that belong to calculated tables that exist for the query.
So I can write colors brands and sales.
I'm missing an S here.
Colors brands and sales amount.
If I did everything correctly and I run this query,
you see that the results contains the brand
and the total amount.
So I'm using SUMMARIZECOLUMNS
over a table that is computed inside the query.
The syntax is very neat
because I can use the table name,
the column name and I can treat those columns exactly
as if they were regular columns.
With that said, calculated tables come with limitations.
You cannot do all the operations
that you could have done in calculated tables.
The first of these limitations
is that you cannot use a calculated table
as the source for another calculated table.
A calculated table cannot reference neither directly
nor indirectly another calculated table.
Let's see that.
If I take this code that right now works and I use it
to define a new table,
I write TABLE,
let's call it brands and sales
and then I write EVALUATE
brands and sales,
now in the definition of brands and sales,
I am using colors brands and sales,
which is another calculated table.
This is not allowed.
If I run this query, all what I obtain is an error
that says that the table colors brands and sales
cannot be used in the current context
because a base table is expected.
You cannot use a calculated table as the source
for another calculated table.
There is also another limitation.
You cannot define columns on these tables.
If you try to, let's use this again.
No.
Slower, let's copy this.
Let's say that I want to define a COLUMN, like
total amount, which is...
No, let's COLUMN, hey color,
colors brands and sales
is large amount
that checks if colors brands and sales amount
is greater than 1,000.
Large, otherwise small.
That should be an IF statement.
So I have created a new column
in the colors brands and sales table
that checks if the amount is greater than 1,000
it's large, otherwise it's small
but this is not allowed.
I cannot define a column in a table
that has been previously created as a calculated table.
If I try to run again this code,
it says that it cannot find the table
colors brands and sales.
Even though the table has been created
a few seconds earlier, it simply does not work.
So calculated tables come with several limitations
and you need to pay attention to them in order to use them.
Beside, there is another important point
regarding calculated tables,
query calculated tables, that is important.
They have their own data lineage.
They do not have the same lineage
as the columns they come from.
Therefore, a calculated table in a query
does not have relationships
with the remaining part of the model.
Let's see that with an example.
Let's start again from scratch.
Let's say that I define a table
that I call colors where I store VALUES
of product color.
And then I want to use this table
to add a new column that contains the sales amount.
I can do that doing EVALUATE ADDCOLUMNS
colors, amount, which is sales amount.
Now, when I run this query,
what would you expect as a result?
Let's format the code.
Because colors contains values of product color,
I would expect amount to compute the sales amount
of the given color
but we need to remember that colors
is a calculated table that has its own lineage.
Therefore, the column color inside the table colors
is not the same color in product colors.
Indeed, if I run this query,
you see that it shows the grand total everywhere.
So there is a big difference between variables and tables.
A variable keeps the data lineage of the columns
that you are using to define the variable, a table does not.
Let's see that with a further example.
I have my TABLE colors.
Let's define a VARIABLE colors.
And let's call this T colors and this V colors.
Now, if I use ADDCOLUMNS of T colors,
you see that I have an error.
DEFINE VAR V colors.
If I use T colors,
I still have the grand total.
If I use V colors,
I have the amount of the given color.
So using a table or using a variable leads
to a very different result.
And you need to pay attention to data lineage
also when you try to merge together these tables.
Let's say, for example, that I want to generate a new table
that contains both the amount coming from this query.
So I can have this code and this code.
One coming from T colors
and the other one coming from V colors.
I run it and I have two results, color and amount,
another color and another amount.
Let's call this amount from table.
I need two different names.
And amount from variable.
So amount from table and amount from variable.
What I want to do is create a new table
that contains color, amount from table
and amount from variable all together
in a single result set.
I can do that, there is NATURALLEFTOUTERJOIN that does it.
So I can EVALUATE...
the NATURALLEFTOUTERJOIN
of this table and this table.
I would like this to work
and to build a join based on the color columns
but if I try to run it, I have an error.
Why that?
Well, again the problem is the data lineage.
In T colors, the column color has its own lineage,
whereas in V colors has the lineage of product color.
So if I use ADDCOLUMNS this way,
that is not going to work.
I cannot join the two tables together.
What I need to do is use, instead of ADDCOLUMNS,
use SELECTCOLUMNS in order to break the lineage.
So if I replace that with SELECTCOLUMNS
from T colors,
I take my column amount from table
and I add color, which is product color.
And I do the same here
but I start from V colors.
By the way, in V colors, I need to use product color.
In T colors, I cannot use product color.
I need to use T colors color.
If I run the query, I will still have,
well, apart from this comma,
I will still have an error
because I have not yet broken the lineage
but now I can do that.
I add an empty string here,
an empty string here.
Now this color has its own lineage.
This color has again no lineage.
So I can run the query and I obtain the result,
which is wrong, yes because I'm using
amount from table in both places.
This needs to be amount from variable.
And now I have amount from table,
amount from variable joined correctly, using the column.
I need to pay attention to the data lineage
whenever I work with calculated tables.
As you have seen, you can now use the DEFINE TABLE statement
to define calculated tables that exist only
for the lifetime of a query.
It's a very nice syntax.
It leads to very clean code
because you can use table name and column name
as you would do with a regular table in the model
but there are several limitations.
The tables created in this way cannot reference each other.
So you can only create one level of tables.
And the second point is that these tables,
they do have their own data lineage.
Therefore, they do not have relationships
with the remaining part of the model
and because you cannot create relationships,
which are local to a query,
this strongly limits the usage of calculated tables
inside your code.
But if you can live with this limitation,
they are a great tool that you can use
when writing DAX queries.
Enjoy DAX.