- Ciao, friends.
I want to show how to improve temporal line charts
in Power BI using DAX.
In this line chart, I have one point per month
and as you see the legend doesn't show me
all the months, just a few months.
What is more important, if I change the size
of the chart here, I still keep all the points of the chart
in the same window and I see that the legend
is reduced to just the name of the two years, 2008 and 2009,
'cause there is no space to display
the name of all the months.
I want to obtain the same behavior in my chart
but as you will see, this is not a behavior
we can obtain by default; let me try to explain.
Here I have just the measure sales amount in the line chart,
that already has the right settings for the visualization,
and I include here in this visual my hierarchy calendar.
The hierarchy calendar has year quarter and month.
And at the year level, I see just three points
with the legend with the year.
But this is not the chart I wanted.
I wanted the chart by month.
So I drill down at the quarter level and the month level.
So far, so good.
I have all the points of my chart here, it works.
But I don't like too much this legend,
that has too many data.
Moreover, if I reduce the size of this chart,
what happens is that at a certain point,
I no longer see all the points in the window
'cause I see a scroll bar.
Why this is happening?
This is happening because the chart I'm using,
the line chart I'm using has a property
for the axis that is called type, you see here?
And when the type is categorical...
every point must be visible.
Every name in the chart must be visible.
And this enforces the chart to create a scroll,
but if there is not enough space.
What we want to do, we want to get the result that we have
when the type is continuous.
But if I try to set continuous here,
it doesn't work.
You see that even though I change to continuous,
it is reset to categorical.
Why this happens?
This happens because in my model,
year quarter and month are strings
and not numbers or dates that can be used
for a continuous line chart.
So in order to solve this problem,
I can try to replace my hierarchy with a date.
I have a date column in my date table.
So instead of using
this calendar here,
I can try to use the date.
And now, the date doesn't work 'cause...
this is not the chart I wanted.
I wanted a chart with one point per month.
Here we have one point per date.
However, we see that now this works
'cause the default setting for the date is,
let's take a look at the type: continuous.
So we solved half of the problem.
But how can we get a continuous line chart
with the month granularity?
Well, I could create a column that has a date
that is always the last day of the month for every day.
This way, that column should work.
So if I go to the date table,
and I create my column, year month that always has here
the visualization shows just the name of the year
and of the month of the year.
But the formula for this column is a date.
This is end of month date date zero,
which means go to the end of the month of the current date.
I see the name 'cause I'm using the format.
The format says,
"Oh, I want to display the name of the month
"followed by the year."
But what is important the data type is date.
Because this data type is date,
if I go back to my chart here,
I can replace the date I have here
with the year, month calculated column
which is a date but is always the same date for every month.
And now I have the same chart
I have shown you at the beginning.
And I can resize this chart, getting the result I expected.
So now you should be able to customize
your line charts according to the granularity you want.
Enjoy DAX!