ISINSCOPE
Returns TRUE when a column is at a level of a matrix or hierarchy that is shown in the current row or column.
ISINSCOPE(<columnName>)Returns: TRUE or FALSE (a scalar).
What it does
ISINSCOPE tells a measure which level of a matrix it is sitting on. In a matrix with rows for category and then subcategory, the subcategory cells are in scope of the subcategory column, and the subtotal rows are not. This lets one measure print a label such as "Subcategory", "Category" or "Total" on the right row. It works with hierarchies and with rows and columns in a matrix.
Syntax and arguments
- columnName: a column that is one of the row or column headers in the matrix, such as
Product[ProductSubcategory].
Example
A margin matrix has product category on the rows, with subcategory under each one. The row label should say which level the row represents, so the reader knows whether it is a subtotal.
Row Level =
IF(
ISINSCOPE(Product[ProductSubcategory]),
"Subcategory",
IF(
ISINSCOPE(Product[ProductCategory]),
"Category",
"All products"
)
)
On a subcategory row, the first test is true. On a category subtotal row, it is false and the second test is true. On the grand total, both are false, so the label reads "All products".
Example: a second, different use
Show the subtotal margin only at the level that is meaningful, and blank the detail elsewhere:
Margin At Category Level =
IF(
ISINSCOPE(Product[ProductCategory]),
[Gross Margin %]
)
The measure only appears on category rows and totals, which keeps a cluttered matrix readable.
Things to watch
- ISINSCOPE only makes sense inside a matrix, a table or a visual that has the column on its rows or columns. Outside one it returns FALSE.
- Test the most detailed level first. A subcategory row is also in scope of the category, so the order matters.
- The result depends on the visual. Moving a column to a different place changes the answer.
- Use it for labels and formatting, not for filtering the data. For the filter, use CALCULATE and the column itself.
- Test the grand total separately. It is in scope of no column in the matrix.
In business terms
ISINSCOPE builds the layout of a finance matrix: the subtotal rows, the labels that read "Division" or "Department", and the grand total line that a controller checks first. It keeps one measure correct at every level of the report.