Hierarchy Slicer Affecting Measure

[TWynn22]

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 slicer:

The slicer is on a page with a Matrix visual.  The matrix has User Full Name on the rows and Year/Month on the columns.  The values are the number of cases closed for that month and a measure which computes the ratio between the number of cases closed and the average number of cases closed that month:

m_CasesClosedPercentageOfSolutionArea = 

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

If I select three managers and all of their children, the measure works as expected and calculates the correct ratio.

The problem occurs when I de-select one of the child nodes in the slicer:

The measure computing the ratio is now producing the incorrect result:

After a lot of investigation, it appears that when de-selecting the child node, an additional filter is now being placed on the Manager.  When the measure calls REMOVEFILTERS on User Full Name, the Users table is being filtered by the user's Manager name such that only that manager's users are available.  I want all of the selected manager's users to be included when the measure is evaluated.

I can call REMOVEFILTERS on User Calculated Manager Name, but that is going to remove all the filters, including the ones coming from the slicer, which will also produce the incorrect result.  I'm not sure why de-selecting the child node causes each row in the matrix to be filtered by both User Full name and Calculated Manager Name.  I am hoping to create a measure that works when a child node is de-selected, but haven't been able to find a way currently.  Any ideas would be most appreciated.  Thanks!

[v-xiandat-msft]

Hi @TWynn22 ,

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.

[TWynn22]

Hi @v-xiandat-msft , I've shared a sample report here.  It's a very simple sample that demonstrates the issue I'm seeing.

The report has three tables:

• Cases
• Dates
• Users

The report has a matrix visual UserFullName slicing the rows and Date Month/Year the columns.  There are four values in the matrix:

1. m_CasesClosedCount - Measure which determines the number of cases closed
2. m_AverageCasesClosed - Measure which determines the average number of cases closed.  This is the measure I'm having an issue with
3. m_CasesClosedPercentageOfSolutionArea - This measure uses the result of m_AverageCasesClosed to determine the ratio of the cases closed by the user to the average cases closed
4. VisCalc - A visual calculation I added to the matrix to show the desired results

When you open the report, the slicer should have the managers Bob and Helen selected and all of their reports:

If you view the matrix, you can do the math and see that 8.89 is the correct average for the users displayed in the matrix.  You will also notice that the measure and visual calculation match.

No, deselect one of the manager's reports, say Frank.  You will notice that the values change:

The m_AverageCasesClosed measure shows two different values.  If you notice, all employees of Bob show a value of 8.60 and all employees of Helen show 9.25.

In fact, you can see that the averages are for employees who report to the same manager.  If you add up Carl, Dan, Gail, and John's cases closed and divide by 4,  you'll see it is 9.25.  Similarly for Bob's reports.  This is not the case if you select all of the manager's reports.

It seems that deselecting one of the reports causes an additional filter to be applied to each row that the manager must equal the employee from that row's manager.  This is the issue I'm seeing and would love to be able to resolve using a measure.  I was able to get the visual calculation to work, but I have many visuals where I need to show this or a similar result and would rather not have to recreate the visual calculation for each one.  Thanks!

[TWynn22]

Hi @v-xiandat-msft , 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!