In our daily office work, we use Excel to ease our work, and people who know the use of PivotTable can generate reports with a fingertip. But in PivotTable there have few limitations that you can’t use Functions. So here comes PowerPivot for Excel to give advanced flexibility over PivotTable.

In PowerPivot for Excel and Power BI, you can write measure to give flexibility in report generating.

So how this title is related to the above discussion. In PivotTable most of the time we use Subtotal, Grand Total, and want to display different results than in row label.

In the below example blue circle in “No of Trade” column, you may want it blank. But In PivotTable, you can’t do it, you need the help of measure. In this case, ISFILTERED function becomes handy in the calculation of measure.

To achieve the above requirement you need to understand the below measures.

ISFILTER_Week =
ISFILTERED ( Sales[Week] )

ISFILTERED_TradeID =
ISFILTERED ( Sales[TraderID] )

ISFILTERED returns True when there are direct filter or filters applied on the specified column.

ISFILTERED can check whether a column is being filtered directly or if any of the columns of the table is being filtered directly.

ISFILTERED supports a table argument since SSAS 2019 or Power BI April 2019. Former versions only support the column name argument.

Check the below output for these two measures. ISFILTER_Week returns True in the subtotal label of Week column but ISFILTERED_TradeID returns False in the subtotal label of Week column. To understand this behavior or ISFILTERED Function look at “No of Trade”/Sales column value in the PivotTable. In “No of Trade”/Sales column subtotal rows values only showing for Week Column subtotal not for TradeID. By this behavior, we can say that when there is any value for the respective column in the Values field then a column is consider being filtered directly.

Now by using the above conditions in a measure we can achieve the subtotal label blank. Check the below measure and output for a clear understanding.

No of Trade (ISFILTERED) =
IF (
    AND (
        ISFILTERED ( Sales[Week] ) = TRUE,
        ISFILTERED ( Sales[TraderID] ) = FALSE
    ),
    BLANK (),
    COUNTX ( Sales, Sales[TraderID] )
)