- Ciao friends, in this video
I want to show how to create a chat friendly data table
in Power BI.
First of all, why this could be useful.
Let's say you can look at an example I prepared
in this line chart, we have many points
one for each month, but not all the points are visible.
I have to use a scroll bar to see all the points
in this chart.
However, if I enlarge the sides of the visual,
I can see all the points.
The reason is that the engine is trying to display
all the names of all the points.
And the reason for that is the access type
for this line chart is a categorical one.
When we use the continuous definition
the continuous type, we will see the entire number
of points of the visual in the same visual
but this is not possible with this data type.
This is not possible with the month name
because the month name is a string.
So there is actually a way to solve this problem
and it is: if I create a column -
so let's take a look at a classic date table here -
The classic data table is defined
in a way where I add a column
that is always the last day of the month.
And here if I look at the content here,
you see that for March, all the dates in March
have the same end of month.
And if I use this column in the chart, it just works,
because this is a column of date data type.
And if I go back to my chart and I replace here
the column that I'm using in this chart year month,
I moved this and I use the end of month
that I've shown you before.
You see that now all the points are visible here
and I can reduce the side of the screen
or the side of the visual.
And I always see all the points.
You can also see that the legend, the sorry, the access,
now this place the two years, 2008 and 2009.
But if I enlarge this, I see all a few names
of the month just to have a visual indication
of the position of the month.
But of course, when I move this around in the tool tip
I see what I see the data that I defined in my date table.
So, can I do something better?
Well, actually yes,
I could use use the date column,
displaying the name of the month instead of the date
that you see here.
But if I do that, I can do more.
I can also replace the original month name column
with a date that displays the month name
in the form of string, but actually has - as a content -
a date.
So this is what we did in the data table here.
So let's go to this definition of the date table.
So I'm gonna show you, sorry, the table here, here we go.
And this is the definition of the date table.
Now, the first thing that you notice
is that now I reduced the number of colors
that I have in the date table.
And if we focus on the year month column here,
where I have the definition of the year month,
I define this column as an end of month.
Like the previous column in the previous table,
but what is the difference here?
If I look, if I select this column here
you see that the data type now is daytime
but the visualization is still a string.
How can I obtain that?
Well, instead of displaying this string,
using the format of the classical format
that we have here, we change the custom
from a string that we can customize now
also for the dates in Power BI using ddd yyyy here we go.
Now I have only the, sorry, I use ddd.
I wanted to use mmm
but this is just a way to show you that if I use
the three the triple D is the name of the day of the week.
If I use the three triple M,
I get the name of the month with three letters.
If you wanted the entire full name
you could just use the four letters
with the four Ms.
In the article below you can see more details
and pointers to - how you can where -
how you can define the correct format string.
So let's go back to this definition.
Now, the advantage of this approach is what
if I go to the visualization,
so let me go back to the report.
And I go to the visualization that I have
with the date table that has shown you now, not this one
but this one, you see that I already have the visualization.
That is what I need.
So I have a visualization with all the points like we did.
We have seen before, but more important in the tooltip,
now you see exactly the name that we used
in the custom format string, so we can actually control this
But there is a second advantage.
If you think about what I did
I no longer have to add a second column just to display
just to define the sort order of the month names.
This column is already sorted.
I don't need a sort by column.
I don't need a second column.
This simplifies my date table, and also the DAX code.
If you want to create a calculation in DAX
where you manipulate or remove the filters
from the month you have to just use the month column
instead of the month and the month number.
Now, if we go one step forward
you can see that at an extreme
I can reduce the number of columns
also doing - using the same approach for the name of the day
of the week or the name of the month using only 12 months.
Now, if you think about this, there is a problem.
If I want to display the day of the week,
if I extracted the day of the week from the current date,
eh, I have a string.
If I want to display this as a date
and I want to use the format string to display the name
of the day of the week.
I cannot use just the original date
because if I do that, I will see Monday the same name
for different underlying dates
but this will not produce for example
a chart where I have only seven days
because I want to group all the dates that are Monday
all the data that Tuesday and so on.
So in order to do that, we use a small trick.
The day of the week is a date that is in 1900
and we only obtain the corresponding date
in the same week of 1900 corresponded to the same day
of the week of the current date for each row
of your date table.
And we can use the same approach for the month name.
We only have 12 months for all the years.
And by using these two columns, now look at this table.
This table has only five columns my date table.
And by using this date table
with five columns, I have first a line chart
regular line chart with all the months in all the years,
single line chart all the months all the years.
Second, a line chart for the months in each year
with a different line for each year.
And now if you think about this,
what I have in the x axis is the month name,
which in reality is a a date in 1900,
but I only display the month name.
And finally I have here a line chart with seven days,
and I'm aggregating here all the Thursday(s).
Now you could apply other filters,
you could create more lines, it just works.
But we reduced the using this technique.
We strongly reduced the number of columns
we have in the date table
automatically defining the sort order
without having to use the sort by column.
So this approach thanks to the custom format string
in Power BI that you can apply to the date columns
This approach can simplify your date table
and simplify also the way you create the charts
in Power BI. Enjoy DAX.