- Ciao friends.
In this video, I want to tell you
how to correctly use the HASONEVALUE function,
and the ISINSCOPE functions.
These functions are very similar in their result.
Nevertheless their behavior is very different.
And understanding when to use which is very important
in order to obtain the correct result and a good speed.
Let's start by looking at what these two functions do.
Now, HASONEVALUE returns you True
if the column you pass as an argument
has only one value visible in the current filter context.
ISINSCOPE, on the other hand, returns you True
if the query you are running
is currently grouping by the column.
Let's see why we typically use them
alternatively in a matrix.
If you look at this report, I have a matrix
that is slicing by brand, product brand,
and then I have a slicer
that is filtering only one category,
and another slicer that is filtering two different years.
I have created a few measures to test the behavior.
brand in scope checks IF ISINSCOPE product brand
then it says brand in scope.
Otherwise returns a blank.
On the other hand, brand one value does the same,
produces a similar result, but checks IF HASONEVALUE.
Similarly, I have created measures
for category ISINSCOPE and HASONEVALUE,
and the same for the year.
The three columns that we have in the report.
Let's see of their result.
When I'm here, I'm slicing by Adventure Works.
So, a sale in this place
will have only one value for Adventure Works,
and indeed, if I use brand has one value,
and put it in my matrix,
you see that brand has one value is True
for all the internal rows of the matrix,
but it is False at the grand total,
because at the grand total,
there will be multiple values visible.
And how does ISINSCOPE work?
Well, if I use brand is in scope,
the result is totally identical
because during the execution of the query,
I am currently grouping by brand,
whereas at the grand total, the grouping by is finished.
So it looks like they behave the same way.
Indeed, if instead of using the brand, we use the category,
the result will be different.
Focus on category.
A slicer is currently filtering one category only.
Therefore, category has one value.
Nevertheless, it is not present in the query.
So if I use category, not this one,
if I use category one value,
you see that category one value is always True,
also at the grand total,
because even though I see multiple brands,
I still see only one category.
And what about ISINSCOPE?
Category ISINSCOPE is always False
because the category is not part of the current visual.
If we do the same for the year,
well the year has a slicer which is filtering three values,
no, two values actually.
Therefore, it does not have the value,
and if I use year one value, it returns False everywhere.
And if I use year in scope, it returns False again.
So the behavior is different
between ISINSCOPE and HASONEVALUE.
And the easiest way to see the difference,
is to focus on category.
If you look at brand in scope, and category in scope,
you see that category is never in scope
and the brand is in scope.
And if you look instead at brand one value
and category one value,
brand one value, and category one value,
you see that brand has one value in the internal rows
and multiple values at the grand total.
But category has always one value.
Now, I told you at the beginning,
that the behavior of ISINSCOPE is to tell you
whether the current query is grouping by a column.
How come that a matrix is doing some sort of...
Some sort of grouping?
Indeed, whenever you run a matrix,
whenever you produce a matrix, the engine generates a query.
Let's inspect the content of the query
which is running behind this matrix.
To do that, I only need to enable the performance analyzer
and grab the query.
So, I enable the performance analyzer, start recording,
refresh my visuals, here I have a matrix,
I just copy the content of the query,
and I put that in DAX Studio
to see the content of the query.
We have a filter for the category, a filter for the year,
these two filters are coming from the slicers,
and then I have a query that does a TOPN, it's always there,
and then SUMMARIZECOLUMNS.
SUMMARIZECOLUMNS performs the grouping by
the column that I pass as an argument.
Now, the column that I am using here is product brand.
Therefore, this query is grouping by product brand
with a filter over the category, and a filter over the year.
A cell evaluated here,
so the evaluation that happens here runs in a filter context
that is showing one category and two years
because of the filters.
And it is showing one value for the brand,
because it is grouping by brand,
it is not just filtering,
it is filtering because it is grouping.
So the difference between HASONEVALUE and ISINSCOPE,
is that HASONEVALUE checks that a column has one value
in the filter context regardless where the filter is coming.
ISINSCOPE tells you whether you see fewer values
because you are doing a grouping by.
And this is the most important detail.
HASONEVALUE tells you whether a column has one value.
ISINSCOPE tells you whether you are grouping by that column
regardless of the number of values you are showing.
Now, if you are grouping, for sure...
you are seeing only one value,
but you might see zero values.
Nevertheless, the good thing about ISINSCOPE
is that it is faster than HASONEVALUE.
The reason is, HASONEVALUE
needs to count the number of rows,
whereas ISINSCOPE just checks
whether there is a current grouping, or not, so it's faster.
Nevertheless, don't be fooled.
You cannot use them alternatively, you need to choose
the correct usage depending on your needs.
If you want to understand whether you are grouping
by a column, you use ISINSCOPE.
If you want to check that there is exactly one value,
you use HASONEVALUE.
And I want to show you a query
where the difference between ISINSCOPE and HASONEVALUE
is really important.
Look at this query, which is coming from one visual.
I just massaged it up in order to make it easier to read.
I have a measure that produces a ranking
based on the order number.
And I'm protecting, not with HASONEVALUE,
I want to use ISINSCOPE, because we said it's faster.
So, I produce my ranking, and I want to produce a result
only if the order number is currently being shown.
And then the query filters only one year,
one day, with the date, and then does a SUMMARIZECOLUMN
over product and sales.
So it's grouping by product name and order number,
applying the filter on one day,
and produces the ranking and the sales amount.
Because the measure is protected by ISINSCOPE,
I am expecting not to see results
if I don't have an order number in my matrix.
I can run this query.
Beware that I only use COUNTROWS
because I don't want the full result,
that would be too large.
So I used COUNTROWS to reduce the number of rows returned.
Let me run the query.
And as you are going to see, it's gonna take a lot of time.
This query runs in around 12, 14 seconds.
Why is it taking so long?
The reason is, I am grouping by product name
and order number.
Now, product name and order number,
they are coming from two different tables.
Therefore, SUMMARIZECOLUMNS will produce the full cross join
of product name and order number.
Given a product, not all the order numbers will be visible,
because given an order there is one product,
but given a product there are some orders, not all of them.
So the full cross join produces most of the time,
an empty set.
Nevertheless, I produce a ranking in any case
because I am using ISINSCOPE.
So, is it True or False ISINSCOPE when I reach this point?
Because I am currently grouping by order number,
this query takes forever to run.
As I said, it run in around 14 seconds.
And we can see the result if we look at the server timings.
You see that, actually not 14,
it took 21 seconds on this machine.
The problem, as we said, is the usage of ISINSCOPE.
I'm checking for grouping by.
I should not check for grouping by,
because most of the times I will have zero values, not one.
Let's see what happens if I use HASONEVALUE
instead of ISINSCOPE.
HASONEVALUE will be False every time there are no orders
for a given product.
And if I run the query, protected with HASONEVALUE,
you see that the result comes now in 15 milliseconds.
That is a thousand times faster.
As you have seen, using the correct function
leads to much better results.
Both in terms of speed
and in terms of usability of your code.
Distinguishing between HASONEVALUE and ISINSCOPE,
is very important.
Now, if you like this kind of content come to sqlbi.com.
You will find the full article
describing the technique used here.
You will be able to download the PBIX file
that I am using for this demo so you can play around with it
and learn more about DAX.
Have fun with DAX!
(upbeat music)