双粒度矩阵值处理：如何将子粒度行中非所属层级的聚合值设为空值

Hey there! I’ve dealt with this exact issue when building hierarchical matrices in Power BI—those inherited aggregate values on sub-department rows can be really misleading. Here’s how you can fix it using DAX measures:

The Core Idea #

We need to check which level of the hierarchy we’re currently viewing (department vs. sub-department) and conditionally return the value or blank accordingly.

Step-by-Step DAX Solution #

Assuming you already have existing measures for Target and Actual, create adjusted versions of these measures using the ISINSCOPE function:

Adjusted Target Measure

Target Adjusted = 
IF(
    ISINSCOPE('YourTable'[SubDepartment]), // Check if we're on a sub-department row
    BLANK(), // Return empty for sub-departments
    [Original Target Measure] // Keep the aggregate value for department level
)

Adjusted Actual Measure

Actual Adjusted = 
IF(
    ISINSCOPE('YourTable'[SubDepartment]),
    BLANK(),
    [Original Actual Measure]
)

How It Works #

• ISINSCOPE('YourTable'[SubDepartment]) returns TRUE when the current row in the matrix is a sub-department level entry (since that column is in scope for that row).
• When it’s TRUE, we return BLANK() which will show as empty in the matrix.
• For the department level row, ISINSCOPE returns FALSE, so we fall back to your original aggregate measure.

Example Results #

BEFORE (Original Matrix)

AFTER (With Adjusted Measures)

Notes #

• If your hierarchy is defined in a separate dimension table with a formal hierarchy (not just two separate columns), you can also use PATHITEM or HIERARCHYLEVEL to check the current level, but ISINSCOPE is simpler for this two-level scenario.
• Make sure to replace 'YourTable' and [Original Target Measure]/[Original Actual Measure] with your actual table and measure names.

内容的提问来源于stack exchange，提问作者Will Returns