- ISINSCOPE is a very commonly used information function.
Its goal is to detect whether a column has only one value
or not, much like HASONEVALUE does,
but it also performs an additional check
that is, the column needs to be currently
grouped by a grouping by function
like SUMMARIZE or SUMMARIZECOLUMNS.
It is very commonly used, because Power BI
uses SUMMARIZECOLUMNS as its primary querying function.
Therefore, the columns that you put on the rows
or on the columns of a matrix or of a table,
they are always in scope inside the cells
and they might no longer be in scope at the subtotals.
Let's see its usage in more detail.
In this first example, I'm using SUMMARIZECOLUMNS
and I'm grouping by brand and country region.
And then I just create three column, brand in scope,
country in scope and product name in scope.
They check respectively, if the product brand ISINSCOPE,
if the country region ISINSCOPE,
and in a similar way for the product name.
Both brand and country region
are currently being grouped by, by SUMMARIZECOLUMNS.
Indeed, brand in scope and country in scope is always true,
whereas product name is not in scope,
because it is not being grouped by and beside,
in this case, it also has multiple values.
The scenario becomes a bit different at the subtotal level,
because at the subtotals, as you will see in the next demo,
ISINSCOPE is even more powerful.
A practical example of usage of ISINSCOPE
is to compute the percentage over parent.
The percentage over parent in a hierarchy of levels
computes the percentage of the current value
against the value of the parent.
That is why it needs to detect in which level
of the hierarchy the cell is being computed.
Let's see the code.
Percentage over parent first of all computes
the sales amount for ALLSELECTED
and we store this value in all sales,
this is the grand total of sales in the current selection.
Category sales are computed the sales amount
for the given category, so only for the current category.
And current sales computes the sales amount
in the current cell.
My entire measure will be executed
inside a SUMMARIZECOLUMNS
that does a grouping by category and subcategory.
Then I have a SWITCH that checks in which level
of the hierarchy I am computing the cell.
If the product subcategory ISINSCOPE,
that means I am showing a row at the subcategory level.
And I want to compute the current sales
divided by the sales at the category level
in order to compute the percentage over parent.
If on the other hand, the category ISINSCOPE,
then I want to compute the current sales
divided by all sales, so at the category level,
I will show the percentage of the category
against the grand total.
And you can see the result here,
for the category audio I'm showing the subcategories.
For each subcategory the sales amount
and the percentage over parent shows three values
that summed together lead to 100%.
Because here we consider 100% the sales amount of all audio.
For the subtotal of the category audio,
on the other hand, the subcategory will not be in scope.
So we will go for this branch.
And we show the percentage of audio against the grand total.
So ISINSCOPE is very commonly used
whenever you want to perform different calculations
depending at which level of the hierarchy you are on.
There is a word of warning about ISINSCOPE
because it works exactly as it is expected to work.
But much like ALLSELECTED does,
ISINSCOPE needs to determine the level
of the hierarchy that you are currently browsing
by deriving this information from other places,
because DAX does not contain
even the concept of a hierarchy, so how does it work?
ALLSELECTED has a much more complex algorithm,
it analyzes the current filter context
and checks that first, the column has only one value
in the current filter context, then, the one value,
so the filter which is filtering only one value
is either coming from a context transition
so an iterator like ADDCOLUMNS,
or a filter which has been created
by a grouping function like ADDCOLUMNS.
And this filter needs to be still active.
So the filter created by either SUMMARIZECOLUMN
or context transition should have not been replaced
by another filter.
We can see the result here.
I'm using ADDCOLUMNS to iterate over the color.
ADDCOLUMNS is an iterator therefore
it generates a row context
that CALCULATE transforms into a filter context,
so a filter context that was generated
by context transition.
If I check ISINSCOPE product color,
in the first calculation I use also another filter
that does FILTER VALUES of product color TRUE.
This filter actively replaces
the filter generated by context transition.
So the filter generated by context transition
will no longer be active, and as a consequence
this ISINSCOPE will be false, you see the result here.
If on the other hand I use
KEEPFILTERS on the same expression,
KEEPFILTERS keeps the previous filter active,
it adds a further filter, but the filter
that was generated by context transition
is still active in the filter context
and therefore, ISINSCOPE returns true.