Power BI中度量值作为切片器无法正常过滤矩阵问题求助

Core Issue #

Your current filter measures only evaluate individual row revenue against the filter range, ignoring hierarchical visibility rules. This causes parent rows to appear if any child row meets the filter—even if the parent's total revenue doesn't. The desired behavior is:

• A Reseller is visible only if its total revenue falls within the filter range
• A Contract under a visible Reseller is visible only if its total revenue falls within the range
• A Year/Quarter/Month under a visible Contract is visible only if its revenue falls within the range

Step-by-Step Fix #

1. Adjust the Revenue Filter Calculated Table #

Ensure your filter table covers all possible revenue values by removing context with ALL():

Revenue Filter = GENERATESERIES(0, CALCULATE([Revenue], ALL()), 100)

2. Create Hierarchy-Specific Revenue Measures #

Add these measures to calculate revenue at each parent level:

-- Total revenue for the current Reseller (ignores Contract and Date filters)
Reseller Revenue = CALCULATE([Revenue], ALL('Dim Contract', 'Date'))

-- Total revenue for the current Contract (ignores Date filters)
Contract Revenue = CALCULATE([Revenue], ALL('Date'))

3. Create the Hierarchy Filter Measure #

This measure validates the current level and all parent levels against the filter range:

Hierarchy Revenue Filter = 
VAR MinVal = MIN('Revenue Filter'[Value])
VAR MaxVal = MAX('Revenue Filter'[Value])
VAR CurrentRev = [Revenue]
VAR ResellerRev = [Reseller Revenue]
VAR ContractRev = [Contract Revenue]

RETURN
SWITCH(TRUE(),
    -- Validate Date level (Month/Quarter/Year): all 3 levels must pass
    ISINSCOPE('Date'[Month]) || ISINSCOPE('Date'[Quarter]) || ISINSCOPE('Date'[Year]),
        IF(ResellerRev >= MinVal && ResellerRev <= MaxVal && 
           ContractRev >= MinVal && ContractRev <= MaxVal && 
           CurrentRev >= MinVal && CurrentRev <= MaxVal, 1, 0),
    -- Validate Contract level: Reseller and Contract must pass
    ISINSCOPE('Dim Contract'[Contract Intermediary Account Name 1]),
        IF(ResellerRev >= MinVal && ResellerRev <= MaxVal && 
           ContractRev >= MinVal && ContractRev <= MaxVal, 1, 0),
    -- Validate Reseller level: only Reseller revenue needs to pass
    ISINSCOPE('Dim Reseller'[Reseller]),
        IF(ResellerRev >= MinVal && ResellerRev <= MaxVal, 1, 0),
    -- Default: hide rows that don't match any hierarchy level
    0
)

4. Apply the Filter to the Matrix #

• Open your matrix visual's Visual filters pane
• Add the Hierarchy Revenue Filter measure and set it to is equal to 1

How It Works #

• The measure first identifies which hierarchy level each row belongs to
• It checks that the current level's revenue and all higher-level (parent/grandparent) revenues fall within the filter range
• Only rows where all relevant levels meet the criteria are kept visible

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