In Power BI, I’ve often had a requirement to not show subtotals for a particular measure in a report. This is usually the case where the calculated subtotal would be a nonsensical figure.
I’ve recently had to do this sort of calculation for a report I’m building, and a mental block lead me to do a Google search for the DAX calculation. Most sources for this will recommend using either ISFILTERED or HASONEVALUE – Don’t do this. It’s wrong!
Here is the flawed HASONEVALUE version of the DAX measure:
Profit Sum HASONEVALUE =
IF(
HASONEVALUE(financials[Product]),
SUM(financials[Profit]),
BLANK()
)And similarly, here is the ISFILTERED version:
Profit Sum ISFILTERED =
IF(
ISFILTERED(financials[Product]),
SUM(financials[Profit]),
BLANK()
)Both of the above are not recommended!
Both of these methods have a major flaw that will display the subtotal when it shouldn’t be shown.
The correct method is to use the ISINSCOPE function as follows:
Profit Sum ISINSCOPE =
IF(
ISINSCOPE(financials[Product]),
SUM(financials[Profit]),
BLANK()
)The structure of these 3 versions of the measure are very similar, however they do return different results:
- All 3 will return the same result when there are no filters applied on the dimension (in the example above the dimension is financials[Product]) being displayed in a visual:
- ISFILTERED will incorrectly display a value when if a filter is applied to a visual:
This is because as the name implies, a filter is applied to the column we’re subtotalling. - Both HASONEVALUE will display a subtotal if only 1 row is being subtotaled:
In this case ISFILTERED is also showing a total as I have a filter applied to the matrix. - ISINSCOPE works in all scenarios, because when the subtotal is calculated on the visual, it’s not taking the Product column into consideration and is by definition out of scope.
The source code for this can be found on Github:
https://github.com/comm9200/Power-BI-Demos/blob/main/Calculate%20Blank%20Subtotals%20The%20Correct%20Way%20Using%20ISINSCOPE.pbix
