New DAX function: ISINSCOPE

A new DAX function is coming in the November 2018 release of Power BI Desktop: ISINSCOPE. You can already try the new function in Power BI service and Azure Analysis Services. In this blog post, I am going to show where ISINSCOPE can be useful.

DAX Guide already includes the new function. Here’s its description from the website: “Returns TRUE if the column is in included in the filter context and it is a grouping column for the current row in the result set.”

While this may distantly remind you of ISFILTERED or ISCROSSFILTERED, there is an important difference that is easier to explain with an example.

Sample data model

Consider the following table called Cities:

Country City Population
Australia Sydney 5,131,326
Australia Melbourne 5,000,000
New Zealand Auckland 1,695,900
New Zealand Wellington 422,700

And the following measures:

City Is In Scope      = ISINSCOPE ( Cities[City] )
City Is Filtered      = ISFILTERED ( Cities[City] )
City Is Crossfiltered = ISCROSSFILTERED ( Cities[City] )

When we put the measures in a matrix together with Country and City, we will get the following result:

At this stage, ISINSCOPE behaves like ISFILTERED. ISFILTERED and ISCROSSFILTERED give you different results, as expected: when you filter Country, you crossfilter City too, because they are in the same table.

The difference between ISINSCOPE and ISFILTERED becomes clear as soon as we filter by City:

Note how ISINSCOPE returns FALSE both at the Country level and at the total level, while ISFILTERED and ISCROSSFILTERED always return TRUE. The latter two functions return TRUE because our slicer filters City.

ISINSCOPE is handy when you need to target the total level specifically. The usual way to check whether something is a total is by checking if a column is filtered. As you can see in the screenshot above, this does not always work.

Use cases

When can ISINSCOPE be useful? The classic SQLBI article, “Clever Hierarchy Handling in DAX“, immediately comes to mind. In case you haven’t read it, I encourage you to do so. In the article, one of the scenarios was showing the percentage of total with applied filters. At the time the article was written (over six years ago!), ISINSCOPE was not available, so Alberto duplicated columns and used them in a hierarchy, while slicing by the original columns. ISINSCOPE allows you to use a single set of columns.

I will use the following five measures to highlight the difference:

Total Population = SUM ( Cities[Population] )

Population AllSelected City = 
CALCULATE (
    [Total Population],
    ALLSELECTED ( Cities[City] )
)

Population AllSelected Country = 
CALCULATE (
    [Total Population],
    ALLSELECTED ( Cities[Country] )
)

Ratio to Parent ISFILTERED = 
SWITCH (
    TRUE (),
    ISFILTERED ( Cities[City] ), DIVIDE (
        [Total Population],
        [Population AllSelected City]
    ),
    ISFILTERED ( Cities[Country] ), DIVIDE (
        [Total Population],
        [Population AllSelected Country]
    )
)

Ratio to Parent ISINSCOPE = 
SWITCH (
    TRUE (),
    ISINSCOPE ( Cities[City] ), DIVIDE (
        [Total Population],
        [Population AllSelected City]
    ),
    ISINSCOPE ( Cities[Country] ), DIVIDE (
        [Total Population],
        [Population AllSelected Country]
    )
)

If we now use some of the above measures and filter by Auckland, Sydney and Wellington, we will get the following results:

Note how ISINSCOPE calculates the percentage at the country level correctly without any use of duplicate columns. Also, total is blank as expected.

Download sample workbook: Cities ISINSCOPE.pbix

If you want to use the sample workbook, you will need to publish it to Power BI service, unless you already have the November 2018 version of Power BI Desktop 🙂

  • Jes Hansen

    Hi Daniil,

    it seems to me that the ISINSCOPE function is to the internal filter context what CALCULATE(ISFILTERED(), ALLSELECTED()) is to the external filter context.

    Internal filter context: rows, columns.
    External filter context: Slicers, filters.

    CALCULATE(ISFILTERED(Cities[City]), ALLSELECTED(Cities[City])) will return true if Cities[City] is selected in a slicer or filter.

    ISINSCOPE(Cities[City]) will return true if Cities[City] is in the current filter context from rows or columns in a pivot table/Visual.

    The next few days will be exciting seen from a DAX perspective I think.

    Jes.

    • Great comment, Jes! I rarely think of filter context as external or internal — makes sense though!

  • Vadim Gerya

    Does anybody know any workaround (except Alberto’s column duplication) to use in older Excel 2013-2016 PowerPivot. Excel 2013- 2016 does not support ISINSCOPE and ISFILTERED is not enough to get hierarchy level when outer filter/slicer used. I spent 3 days and failed to find solution to get hierarchy level… :(((

  • Anna J.

    Hello Daniil.

    I was wondering if ISINSCOPE function can do the following:

    I am using a Matrix and would like to show the fourth quarter (Q4) value next to the year when I click collapse button. At the moment when I click collapse I’m getting a total of all four quarters. Please be so kind and let me know if this is possible.

    Thanks in advance Daniil.
    https://uploads.disquscdn.com/images/ef6afb032e3f2558536c85db9b584aa736ce3c4d82a59f0b648db8bcb01a6869.jpg