Ciao friends.
In this video I want to show you
how to use conditional formatting and some DAX code
to easily find the minimum and maximum values in a matrix.
Let's start looking at the scenario.
If you look at this report I'm using the brand,
the product brand and the store continent in a matrix
and I'm using color highlighting to highlight
the values ... to highlight the numbers
based on their relative value.
It's not easy to spot immediately
what is the minimum and maximum values of the entire matrix.
What I want to obtain is something different.
I want a report that looks like this one.
You see that I have in green the maximum,
and in red the minimum value and of course being a measure,
it is completely dynamic.
Therefore the spot changes depending on
how the selection is made.
So how can I achieve that?
Well what I need to do is build a measure
that returns 1 for the minimum and 2 for the maximum.
This measure needs to rebuild inside it
the entire set of values displayed in the matrix.
So it needs to build all these values in a variable
then it will compute the minimum, the maximum value
and the current value.
At the end we will check
if the current value is equal to the minimum we return 1,
if it is the maximum we return 2
and then we will use the value of this measure
in conditional formatting in order
to change the background of my number.
Let's start.
First of all I need a new measure,
so I create a new measure, let's call it Min or Max
and let's follow the steps.
First I need a variable that contains the values displayed.
Values displayed will use CALCULATETABLE
because it will need to change the filter context
then we do a SUMMARIZE of sales by product brand
and by store continent.
This rebuilds the structure of the table
but we also need to create a new column
that contains the sales amount.
So we use columns around it and we create a new column
which we call the Amount which is just sales amount.
Now the entire ADDCOLUMNS need to be enclosed
into ALLSELECTED because what I need to build
is the entire matrix, not only the current cell.
The next step is finding the minimum value,
so the Minval is just the MINX over the values displayed
of my variable, of my column Amount
and the same for the maximum value, maximum value is the
MAXX over the values displayed again of Amount.
Last thing that I need is the current value so
current value is just sales amount.
Finally, I need the Result.
The Result is just a SWITCH,
We switch on true because...
we check multiple conditions.
Now if the current value is equal to the minimum value
then we return 1
otherwise if the current value is equal to the max value
then we return 2, otherwise a blank is just fine.
Finally we return the result.
Now this measure returns 1 for the Min and 2 for the Max.
The last thing that I need to do
is use conditional formatting
to read the value of this measure
and behave the proper way.
So I need to go here and go to conditional formatting
and I'm gonna change the default rules.
I want a set of rules,
it's no longer based on the sales amount
but it's based on my Min or Max measure.
If the value is 1 that means we are on the minimum,
therefore we use the red color.
Where is red, here it is.
And we create a second rule that says that
if the value is 2, as a number,
then we should put it on green.
Let's choose any kind of green.
Once I click on OK, now you see that Asia is green
because it's the highest value
and Tailspin Toys in Europe is red.
And if I change the selection of course everything changes.
As we have seen multiple times
combining DAX and conditional formatting
you can easily obtain very interesting reports.
If you are interested in this kind of topics,
come to SQLBI.com.
You will find out the full article describing the technique
and you'll also have the option of downloading the demo file
to start learning DAX.
Have fun with DAX!
(upbeat music)