Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hierarchy Slicer Affecting Measure

Hi, I have a Users table where each row has a User Full Name and User Calculated Manager Name value.  I have created a hierarchy in Power BI using these values: and added the hierarchy to a s...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

The function ALLEXCEPT might be more suitable in this scenario than REMOVEFILTERS. Keeps filters on specified columns while removing all other context filters. This can allow you to maintain the slicer's selection effect on the manager while removing the unwanted filter effect on the User Full Name.

VAR AverageClosedSolutionArea =
    CALCULATE (
        AVERAGEX ( 'Users', [m_CasesClosedCount] ),
        USERELATIONSHIP ( 'Dates'[CalendarDate], 'Cases'[Case Close Date (CT)] ),
        ALLEXCEPT ( 'Users', 'Users'[User Calculated Manager Name] )
    )
VAR ClosedByUser =
    CALCULATE (
        [m_CasesClosedCount],
        USERELATIONSHIP ( 'Dates'[CalendarDate], 'Cases'[Case Close Date (CT)] )
    )
VAR Result =
    DIVIDE ( ClosedByUser, AverageClosedSolutionArea )
RETURN
    Result

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous , thank you for taking the time to look at this.  I tried using ALLEXCEPT() and saw the same results.  I've created a small sample report which reproduces the issue I'm seeing.  Is there any way to share that report with you here?  I don't see an option to attach files.  Thank you!