ISINSCOPE DAX Function (Information)
Returns true when the specified column is the level in a hierarchy of levels.
Syntax
Parameter | Attributes | Description |
---|---|---|
ColumnName |
The column of the level. |
Return values
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.
Remarks
ISINSCOPE checks there is a filter placed on the column derived either from context transition or from a group by column placed by SUMMARIZECOLUMNS, which implies that there are only zero or one value in the current filter context.
When ISINSCOPE returns TRUE, HASONEVALUE might still return FALSE if there are no data for the intersection considered. This could happen in SUMMARIZECOLUMNS, as described in the Distinguishing HASONEVALUE from ISINSCOPE article.
» 4 related functions
Examples
-- ISINSCOPE is useful to detect if a column is currently -- in the rows or columns of a visual (like groupby columns -- of SUMMARIZECOLUMNS) AND it has only one value visible EVALUATE TOPN ( 20, SUMMARIZECOLUMNS ( 'Product'[Brand], Customer[CountryRegion], "Brand in scope", ISINSCOPE ( 'Product'[Brand] ), "Country in scope", ISINSCOPE ( Customer[CountryRegion] ), "Product name in scope", ISINSCOPE ( 'Product'[Product Name] ) ) )
Brand | CountryRegion | Brand in scope | Country in scope | Product name in scope |
---|---|---|---|---|
Contoso | Australia | true | true | false |
Wide World Importers | Australia | true | true | false |
Northwind Traders | Australia | true | true | false |
Adventure Works | Australia | true | true | false |
Southridge Video | Australia | true | true | false |
Litware | Australia | true | true | false |
Fabrikam | Australia | true | true | false |
Proseware | Australia | true | true | false |
A. Datum | Australia | true | true | false |
The Phone Company | Australia | true | true | false |
Tailspin Toys | Australia | true | true | false |
Contoso | United States | true | true | false |
Wide World Importers | United States | true | true | false |
Northwind Traders | United States | true | true | false |
Adventure Works | United States | true | true | false |
Southridge Video | United States | true | true | false |
Litware | United States | true | true | false |
Fabrikam | United States | true | true | false |
Proseware | United States | true | true | false |
A. Datum | United States | true | true | false |
-- In this example we use ISINSCOPE to compute the percentage -- over the parent level in a hierarchy over products DEFINE MEASURE Sales[Pct over parent] = VAR AllSales = CALCULATE ( [Sales Amount], ALLSELECTED () ) VAR CategorySales = CALCULATE ( [Sales Amount], ALLSELECTED (), VALUES ( Product[Category] ) ) VAR CurrentSales = [Sales Amount] RETURN SWITCH ( TRUE (), ISINSCOPE ( 'Product'[Subcategory] ), DIVIDE ( CurrentSales, CategorySales ), ISINSCOPE ( 'Product'[Category] ), DIVIDE ( CurrentSales, AllSales ) ) EVALUATE CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Product'[Category], ROLLUPADDISSUBTOTAL ( 'Product'[Subcategory], "Category total" ), "Sales Amount", [Sales Amount], "Pct over parent", [Pct over parent] ), 'Product'[Category] IN { "Audio", "TV and Video" } ) ORDER BY Product[Category], [Category Total], Product[Subcategory]
Category | Subcategory | Category total | Sales Amount | Pct over parent |
---|---|---|---|---|
Audio | Bluetooth Headphones | false | 124,450.79 | 32.37% |
Audio | MP4&MP3 | false | 170,194.00 | 44.26% |
Audio | Recording Pen | false | 89,873.37 | 23.37% |
Audio | (Blank) | true | 384,518.16 | 8.05% |
TV and Video | Car Video | false | 604,413.71 | 13.76% |
TV and Video | Home Theater System | false | 1,525,526.26 | 34.73% |
TV and Video | Televisions | false | 1,834,257.05 | 41.76% |
TV and Video | VCD & DVD | false | 428,571.27 | 9.76% |
TV and Video | (Blank) | true | 4,392,768.29 | 91.95% |
-- ISINSCOPE, like ALLSELECTED, deduces the information about -- the grouping of a column from a different algorithm: -- the column must show only one value in the filter context -- and the filter must be coming from either a groupby column -- introduced by SUMMARIZECOLUMNS, or from a context transition. -- Besides, the filter must be still active (not overridden) EVALUATE ADDCOLUMNS ( VALUES ( 'Product'[Color] ), "ISINSCOPE 1", CALCULATE ( INT ( ISINSCOPE ( 'Product'[Color] ) ), FILTER ( VALUES ( Product[Color] ), TRUE ) ), "ISINSCOPE 2", CALCULATE ( INT ( ISINSCOPE ( 'Product'[Color] ) ), KEEPFILTERS ( FILTER ( VALUES ( Product[Color] ), TRUE ) ) ) )
Color | ISINSCOPE 1 | ISINSCOPE 2 |
---|---|---|
Silver | 0 | 1 |
Blue | 0 | 1 |
White | 0 | 1 |
Red | 0 | 1 |
Black | 0 | 1 |
Green | 0 | 1 |
Orange | 0 | 1 |
Pink | 0 | 1 |
Yellow | 0 | 1 |
Purple | 0 | 1 |
Brown | 0 | 1 |
Grey | 0 | 1 |
Gold | 0 | 1 |
Azure | 0 | 1 |
Silver Grey | 0 | 1 |
Transparent | 0 | 1 |
Related articles
Learn more about ISINSCOPE in the following articles:
-
Filtering the Top 3 products for each category in Power BI
This article describes different techniques to display the first three products for each category in Power BI. It includes considerations on how to adapt the technique to different models and requirements. » Read more
-
Showing the top 5 products and Other row
This article shows how to add an additional “other” row to the selection obtained using the Top N filter in a Power BI report. » Read more
-
Distinguishing HASONEVALUE from ISINSCOPE
This article describes the differences between HASONEVALUE and ISINSCOPE, which are two useful DAX functions to control the filters and the grouping that are active in a report. » Read more
-
Introducing the RANK window function in DAX
RANK is a new DAX function to rank items based on multiple columns. This article introduces the RANK function and its differences with RANKX. » Read more
Related functions
Other related functions are:
Last update: Oct 3, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/isinscope-function-dax