- Ciao, friends.
In this video, I want to show you
how to optimize a report
that contains a lot of visuals inside it.
This time, the problem will not be DAX,
so we are not going to learn anything new about DAX,
even though I will be able to push some DAX details
about auto exist inside the session.
But the topic is understanding why a report is slow
and once we learn that the problem is the presence
of too many visuals in the report, how to solve it.
This time, we are going to use a custom visual by OKVIZ
that will enable small multiples on card visuals.
Let's get started.
Let's start understanding how I built my dashboard.
What I want to show is the margin percentage
for different brands and for different colors.
So I start with a simple card visual
where I show the margin percentage as a measure
and Adventure Works as the category label.
Now, the category label comes from the measure name.
That's why I built a set of measures,
which you can see here, A. Datum, Adventure Works,
Contoso and so on where each measure computes
the gross margin percentage for one given brand.
And then I put the measure here.
I colored it nicely and the last thing that I need to do
is to filter only one color
because I want one card for each brand and color.
So I added a filter on the visual
that says that the color needs to be red.
Now, this looks like a small detail
but it's going to be important later
when we change the structure.
So one filter on the visual to filter the color,
the measure there and it works just fine.
I can select whatever. One card works.
The thing is, I want to build a more complex dashboard
that shows all the different colors,
so I have red products, black products and blue products.
Let's get rid of the filter so it's larger.
And all the different brands.
Now, this dashboard right now has two issues.
The first one is that it's really ugly but I cannot help it.
I'm definitely not a graphic designer.
So my task is to make numbers correct and fast.
You will make them pretty.
So I will not solve the problem of the dashboard being ugly.
What I will do is solve its speed problem
because if we look at the speed of this dashboard,
you see that it's just okay.
When you click on one year, it takes some time
in order to refresh it and if I click on another year,
I have to wait one, two, around three seconds
for the dashboard to refresh.
And first, we need to understand where the problem is.
The problem might be DAX, the problem might be in the number
of visuals, the problem might be everywhere.
Whenever you want to analyze the performance of a dashboard,
the first thing to do is launch the performance analyzer
and start gathering information
about why the dashboard took so long.
So we are going to do exactly that.
Let's go to View and enable the performance analyzer.
Start recording and refresh the visual.
See how many cards I have
and they take some time to refresh.
1.4 seconds, 2.3 seconds, around 1 second.
Each card took a lot of time.
So still the problem might be DAX
or it might be somewhere else.
If we expand each individual card,
we see the most important information
that will drive us to the conclusion
that the problem is not DAX.
For every visual, you have the DAX query
that is the time needed to execute the query,
the visual display, that is the time needed
to produce the visualization and finally, this other.
Other accounts for the vast majority of the time.
Out of 1.4 seconds, 1.3 is spent in other.
Now, what is that other?
Other is the time that is required, the time that is spent
for the visual waiting to start, waiting to run its query.
So it's just wait time.
It's not time where the computer is doing anything.
The Power BI engine cannot run all the queries
for the visuals at the same time.
It need to schedule them so it run a few in parallel
and then as soon as the first ends, it starts the next one.
So it builds a queue of visuals.
And the time for which a visual is waiting in the queue
before it runs is the other time.
Now, the other time is interesting.
We can sort this by total time.
Let's zoom in a bit.
And you see that the first card,
the first three text box are very fast.
Now, the first card already had to wait half a second.
And if we go to this card, this had to wait 1.3 seconds.
And if we go to the last card,
this had to wait nearly four seconds before running
and run a query that lasted for 16 milliseconds.
So the problem of this dashboard is not DAX.
The problem is the number of visuals.
We just have too many visuals.
So the time that you have to wait
is the time spent in a queue.
If we want to optimize this dashboard,
what we need to do is reduce the number of visuals
and we can just cut some visuals,
say hey, you can only see red products or black products,
but not all of them or we can enable small multiples.
Small multiples lets you multiply one visual by one column,
so you choose one category
and the visual will be duplicated once for each category.
Or in our case, once for each brand.
The thing is, as of now,
the card visual does not support small multiples.
But there is another visual made by OKVIZ,
Card with States, that supports small multiples.
It's free so you can download and start using it.
You need to register in order to enable the premium feature
of small multiples but as I said,
it's free so not a big deal.
And using the Card with States,
we can optimize the model.
So let's do that.
I already have prepared here the first visual.
You see if I click on that,
this is one Card with States visual.
I put the measure.
This time I did not put the measure,
the gross margin of Adventure Works,
I just put the generic measure, gross margin
that divides gross margin by sales amount
because one of the feature of Card with States
is that the category can come
from the category of the small multiple.
So the visual is here.
I just need to enlarge it a bit
because I will then multiply it.
Then I take the brand, add it to the category
and you see what happened,
that now I have all the visuals,
all the different brands
and I have one card for each brand.
The main difference between this visual
and the previous one is that this is only one visual.
So it will start, run the query
and then it will process the visualization part.
But if I have three different colors,
I only need to build three visuals.
Right now, this is not showing only red.
What's happening here?
This is showing everything
because I'm still missing the filter on red.
And here comes a big difference
between using small multiples and using regular cards.
If you remember, in my original dashboard,
each individual visual has a filter.
Let's click on that.
That forces the color to be red
and that filter is applied to the visual.
If I do the same here, I can.
So I can take the color, add it as a filter
but look what happens when I select red.
Right now, I see 11 different visuals.
If I click on red, I no longer see 11 visuals,
I see only nine of them.
Why that?
Well, the problem is that the filter red
is applied at the visual level.
So this is removing any product, any brand
that does not contain red products.
Indeed, we can see from the original dashboard
that, for example, A. Datum shows a blank
and the same for The Phone Company.
These two brands do not have margin.
Therefore, they show a blank if I use that technique
but they completely disappear if I apply a filter
on the color on the visual.
So the technique that I need to use here
is not to use the filter on the color.
Instead, I created a measure, red margin,
that computes the gross margin where the color is red.
I'm applying the filter inside the measure
instead of on the visual.
The advantage is that now this measure,
now the visual will compute the gross margin
for all the brands and show all the brands
even though they show a blank.
Well, sort of.
If I now get rid of this gross margin and I use red margin,
you see it doesn't look like anything has changed
because I still see only nine products
but now I have the advantage that I can control that
because I know that the query that runs the visual
is actually running so I can return...
instead of blank, I can return zero.
If I return zero, now I see A. Datum with 0%
and The Phone Company with 0%.
Keep in mind, the same technique would not work
if you apply the filter before on the visual
because in that case, A. Datum would not even be queried,
so you will never be able to control
whether to show it or not.
The next thing that I need to do
is just duplicate this visual for multiple times.
Let's do that.
I just need to select it, copy and paste it.
Now I have another version of the same visual.
Put it there and let's paste it again
so we have all the three that we need.
Let's align them nicely.
Okay, this needs to be black,
so we go on the card.
Now that should be blue.
We color it in blue.
And finally, instead of using the
red margin, we use the blue margin here.
And the same for the black one.
We change it.
Let's get rid of red margin.
We put the black margin as the measure.
And in the card, we make it black.
Or at least dark enough.
Let's go for this one.
Now I have the dashboard that shows the same data as before
but now it's only showing three visuals.
Let's look at performance.
We launch it, we launch again the performance analyzer.
Start recording, refresh the visual.
And look at the numbers.
Now instead of taking around four seconds,
it takes only half a second.
The DAX query is still very fast.
The visual display accounts for a good amount of the time
and the other time is only 200 milliseconds.
So it is way faster than before
because it basically reduces the number of queries,
which are sent to the server.
So whenever you have problems like this,
so you have a lot of visuals,
using small multiples is likely to be your best friend.
As you have seen, this time, DAX was not the culprit.
We didn't have to optimize DAX at all.
The problem of dashboards with a lot of visuals
is just the number of visuals.
And you can inspect it with the performance analyzer
and then finding a solution, in this case,
meant using small multiples,
which are not currently supported in card visuals,
the official card visual that is provided by Microsoft
but can be enabled with OKVIZ's Card with States.
If you are using, in general, a lot of visuals
that show small variations of the same calculation,
enabling small multiples is the way to go.
Enjoy DAX.