- Ciao, friends.
In this video, I want to show you multiple ways
to solve a very simple problem.
Let's say that you need to create a configuration table
in your Power BI file.
Just a very simple table with two, three rows,
and you want to do it quickly.
As simple as it is, this scenario offers multiple solutions
and it's worth it to learn all of them
because this way you can make a better choice
whenever you need to do that.
Let's see first the scenario
and then different ways of solving it.
I want to create a table like this one,
a very simple table
with three rows and three columns;
low, medium, high
and 0, 10, 100, 10, 100, whatever number.
Because I need to use this configuration table
for whatever reason.
Now, the easiest and probably the most common ways
of creating this table is just to click on Enter data.
You just click here and this opens a nice user interface
from where you start providing the column name
which is price range.
Then we want the minimum price, the maximum price,
and you just type in your data.
Low is between zero and a hundred.
I didn't want this column.
Let's get rid of it.
We have medium between a hundred and a thousand
and we have high between a thousand
and whatever large number.
Finally you provide a name for your table here,
click OK, and the table gets created.
Now, I already have the table here.
So I'm going to click Cancel
because the outcome is exactly what you see here.
You have a new table in the model
with the columns that you created.
The thing is, what happens when you want to change
the content of the table, because you made a typo
or because you want to change some values.
Then, the experience is no longer so nice.
Because at this point if I want to change the content
of the table, the table has been created inside Power Query.
So I need to click on Transform data
and I don't know about you,
but the first time that I had to change a table
I started clicking here and try to type something.
It didn't work.
There is no way you can change the content from here.
If you want to change the content of the table
you need to go at the beginning, at the source,
click here, and this opens again, the user interface
from where you can change the content of the table.
What happens internally is that
whenever you create a table this way
it generates code like this one.
You see that the first row loads from a json document,
does a binary decompress of this crazy string.
This red string is actually the content of your table
compressed in a document that is later read by Power Query.
This technique works fine and there are no issues with that.
Apart from the fact that whenever you want to update it
you need to go through the user interface.
Plus, there is another small detail
if you want the content to be dynamic.
So, for example, the maximum values you want to compute it
by scanning some tables,
you cannot because this is not DAX.
This is M code and the two models are separated.
Therefore, I want to explore with you
different ways of creating the same table using DAX.
Because we are going to write some DAX code
instead of using the user interface we just use DAX Studio.
Now, once you have DAX Studio, you can start writing a query
and then we will move the content inside Power BI later.
The first way to create a temporary table
is to use the ROW function.
So, you can write EVALUATE and use ROW.
ROW requires you to provide a name for the column,
price range, and then a value.
The first was low
and the second column will be the minimum price.
And the first row was zero.
And the second column was maximum price with a hundred.
If I just run this query you see that the result contains
one row, low zero, and a hundred.
That's only one row.
If I want to have multiple rows,
I need to create all of them using ROW
and then union them together.
That is what I can do.
I can just copy all of this
generate another row for medium.
Yes, minimum price is a hundred
and maximum price is a thousand.
Now these are two rows
and I need to union them together.
And finally, I add the third row which is high
with a thousand and whatever sequence of nine.
I can run this query, it works.
Now, this is DAX studio.
How can I move the query back in Power BI?
I'll show you just the first time and then,
once you get the point you can do that by yourself.
If you want to move a query from DAX Studio to Power BI
what you need to do is just copy the content of the query.
I messed up everything, okay.
The content of the query, except the EVALUATE part.
So just copy everything, go here,
and then I can create a new table
modeling new table.
I give it a name price range equals
and then I just copy the content of my query.
Hitting Enter is enough to have the new table available.
I have my price range table.
And you see that I have the full content,
the content of the table here and the DAX code.
You just need to remember to get rid of the evaluate part.
Now, that we have seen the first time
I will just write code in DAX Studio.
You got the point, if you have a query in DAX Studio
you can move it in Power BI in a simple way.
So UNION works fine.
It's just a bit annoying
because you need to repeat the column names all the times
and you write a lot of text
in order to create simple tables.
So, the column names need to be repeated multiple times.
There is another way of creating
static tables which is using DATATABLE.
DATATABLE is a function where you first
need to specify the column names and the data types,
and later you provide the full content
with a lot of curly braces.
Let's see that together.
We need first to specify the structure of the table.
Okay, price range which is a string.
Then we have the minimum price, which is a currency.
And the maximum price, which is again a currency.
Once you have finished the list of pairs,
name and data type, then you start providing the content.
The first curly brace and then a second one.
The first introduce the sequence of rows
and then for each row you need another curly brace.
We have low, zero to a hundred.
And then we have medium
from a hundred to a thousand.
And the last will be high
from a thousand to whatever nines we want.
Then you close the sequence of the content
and you close DATATABLE.
If I run this query,
you see that the result is exactly what I was expecting.
And I can write as many rows as I want here.
It works fine.
There are no problems with DATATABLE.
It only has a lot of these curly braces.
So the syntax is not really standard DAX.
That's a quite old function.
And there is another important detail.
If you use DATATABLE, the content
so, the definition of the content of your table
cannot be computed.
You can create a data table with static values
but you can not write expressions.
So, in this example,
if I want for example here,
to write something SUM of sales whatever,
it will not work - the value needs to be a static one.
With that said, DATATABLE is useful
because it provides the entire code in the Power BI window
so you can change it without having to revert
to Power Query.
There is another way you can still create the same content
which is use the anonymous table constructor.
You just start with the curly brace
and then you start writing low.
Zero, a hundred.
This is the mistake that nearly everybody
including myself do at the beginning.
If you write a table in this way,
what you are creating is a table
containing three different rows.
Because this is a row, this is a row,
and again, this is a row.
What I want to create is a table with three columns
and then three rows.
So, I need to specify that this is indeed just one row.
And I do that by enclosing it in parentheses.
Now, this is one row.
Let's see first the result.
You see that now it's pivoted.
Now I have Value1, Value2, Value3,
and I can provide different expressions.
So we have low, medium, and high
a hundred and then a thousand
and finally, a thousand,
all the nines that I want.
Again, I obtain the same result,
but you notice that the column names are wrong.
It's not that they are wrong,
but they are Value1, Value2, Value3.
This constructor is an anonymous table constructor.
It creates tables
but it doesn't let you create the column names.
You need to specify the column names later
with a SELECTCOLUMNS.
So if you use this
and you want to specify the column names,
you need to embed everything inside a SELECTCOLUMNS.
And then you say, well
I want the first column to be named price range.
And I use Value1.
Then the second column will be the minimum price
and I want to use Value2.
And finally, the max price is Value3.
If I run the query now
you see that the result contains column names;
price range, minimum price, maximum price.
Same result that I obtained earlier.
So you have seen we have multiple ways
of obtaining the same goal.
The problem that I see with using
this anonymous table constructor,
is only that you need a SELECTCOLUMNS outside of it.
And if for whatever reason you want to add columns
then you always to fix Value1, Value2, Value3.
If I add a column in the middle,
I will need to adjust all my column names
in order to provide the proper result.
As you have seen,
a simple operation like creating
a small configuration table in your model,
already offers you a lot of different options.
For sure, you can use Power Query to create it
if you like the user interface.
But if you want to be a bit more flexible
then DAX offers you several options.
For a simple table that you use inside a measure
you can use the anonymous constructor
with curly brackets.
It works just fine,
but you cannot specify neither the column names
nor the data types.
On the other hand,
you cannot use any kind of expression
in the definition of the table.
If you need to specify both the column names
and the data types,
then DATATABLE is the function that does the job.
It works, it provides a complete description of the table
in terms of column names, data types,
and content of the table,
with the limitation that you cannot use expressions.
You need to use constant values inside DATATABLE.
ROW works fine.
There you can specify the column names.
You cannot specify the data types
and you have a limitation
that you can create one row at a time.
Therefore, a table with multiple rows
requires the union of multiple ROW functions.
As you have seen, there is not
one function that does everything.
Each function has some specific functionalities.
You need to choose the one that fits best for you.
Enjoy DAX.