The ISINSCOPE function returns the Boolean True if the column indicated as an argument is the level being considered in a hierarchy of columns, and False otherwise, which allows us to create measures that have different behavior depending on the level of the hierarchy at which are applied.
ISINSCOPE(
columnName
)
- columnName: Name of the column for which you want to find out if it is the level considered or not in a hierarchy.
The ISINSCOPE function returns a Boolean.
To see this function in operation we are going to work with two related tables (although only the second would be necessary): the first one ("Sales") contains sales information including the Amount field that contains the amount of each sale. The second table ("Geography") contains information about locations, including the name of the country, the region and the city:
Now we create a matrix visualization and drag the Country, Region and City fields to the row field to create an implicit hierarchy (we could also have created it explicitly). We then take the Amount field to the matrix to see sales by country, region or city. To make the operation of the ISINSCOPE function more obvious, the matrix is configured to show the + and - symbols to the left of the row headers so that it is easy to expand some headers and not others:
In the previous image we see that sales are being shown for countries (first level of the hierarchy), for regions (second level of the hierarchy) and for cities (third level of the hierarchy). And this is where the ISINSCOPE function is useful: taken to the matrix, it allows us to find out if, for example, it is the City field that is being displayed in one of the rows. We are going to create the following measure:
Nivel =
SWITCH(
TRUE(),
ISINSCOPE(Geography[City]), 3,
ISINSCOPE(Geography[Region]), 2,
ISINSCOPE(Geography[Country]), 1
)
We are simply returning the value 3 if the level being displayed is the one corresponding to the city, the value 2 if it is the region, and 1 if it is the country (in this case the first condition that is met is returned). If we take this measure to our matrix we obtain the following result:
