What is ISSUBTOTAL?
ISSUBTOTAL is a logical function in DAX that returns TRUE if the calculation is being performed on a subtotal row and FALSE if it’s not. It takes one argument, which is the name of the column that you want to check for subtotals.
Why Use ISSUBTOTAL?
Using ISSUBTOTAL can be incredibly useful when you’re creating complex calculations in Power BI. By checking whether a calculation is being performed on a subtotal row, you can make sure that your calculation is only applied when you want it to be. This can help you avoid errors and ensure that your reports are accurate.
How to Use ISSUBTOTAL
To use ISSUBTOTAL, you first need to create a measure that you want to check for subtotals. For example, let’s say you have a sales table with columns for Product, Region, and Sales Amount. You want to create a measure that calculates the total sales for each region, but you only want the measure to be applied at the region level, not at the product level.
To do this, you can create a measure that uses the SUMX function to sum the sales amount for each row, but only if the row is not a subtotal:
Sales by Region =
In this example, the FILTER function is used to remove any subtotal rows from the Sales table, and the SUMX function is used to sum the Sales Amount column for each row. The NOT function is used to reverse the result of the ISSUBTOTAL function, so that the measure is only calculated on non-subtotal rows.
The ISSUBTOTAL function in Power BI's DAX language is an incredibly useful tool for creating accurate and robust reports. By checking whether a calculation is being performed on a subtotal row, you can ensure that your calculations are only applied when you want them to be. If you're new to DAX, or just looking to brush up on your skills, be sure to give ISSUBTOTAL a try.