ALLEXCEPT removes too much

[FrancisSaman]

Hi guys

What I'm trying to accomplish:

In a selected month-year, for a certain employee, I need to count the shifts performed by the employee himself as well as all the shifts performed by his team in that same month-year.

Context:

An employee can work several shifts (in different skills) in a month-year, while his team works more shifts (or less).

Data:

I've got this data in a table (EMPLOYEE METRICS) that looks like this:

Employee Id - Shift Date - Shift Id - Employee Team Id

There's a relation to the Employee table, using the Employee Id column.

Using a date dimension table I'm filtering shifts by month-year.

Measure code:

Me Team Shifts Count =

VAR AtSkillLevel = ISINSCOPE('HR EMPLOYEE_METRICS'[DISPLAY_SKILL])

VAR AtTeamLevel = ISINSCOPE('HR EMPLOYEE_METRICS'[SHIFT_TEAM])

VAR EmployeeDefaultTeam = SELECTEDVALUE('HR EMPLOYEE_METRICS'[SHIFT_TEAM])

VAR Result =

SWITCH(

TRUE(),

AtSkillLevel, CALCULATE(DISTINCTCOUNT('HR EMPLOYEE_METRICS'[SHIFT_ID])),

AtTeamLevel, CALCULATE(

DISTINCTCOUNT('HR EMPLOYEE_METRICS'[SHIFT_ID]),

CALCULATETABLE(

ALLEXCEPT('HR EMPLOYEE_METRICS', 'HR EMPLOYEE_METRICS'[SHIFT_TEAM], 'HR EMPLOYEE_METRICS'[SHIFT_NUMBER])

)

)

)

RETURN

Result

As you can see in the screenshot below, I'm getting the result I need: count of the shifts for the employee, and the shifts performed by his team.

The only issue is that I'm also getting all the other teams, while I only want the team the employee is in.

It looks like the ALLEXCEPT is messing up the number of rows shown by the matrix visual.

The small matrix on the left is what I get without using the measure; but that only gives me what the employee did.

Can anyone help out with getting the matrix on the right filtered to only show the row of Equipa E, ie the employees own team?

Basicly it seems like I need to do another filter on the employee's team after the calculation was applied, but I can't figure how to do that...

Thanks!

Screenshots:

Data modelResult

[JustJan]

Hi @FrancisSaman 

If I understand correctly, you are using the Employeed_Id in the HR EMPLOYEE METRICS table and not from the HR V_EMPLOYEES, therefore there is no filter of the EMPLOYEE_ID (from the HR V_employees table) in place, due to the relationship direction between the two tables. 

The current filter is now only the month-year for the whole visual, on a line-by-line basis there you also have the filter on shift_team and display_skill, depending on the level of the line. 

If you use the (filtered ??) employee_id from the HR V_employees it should only show you the filtered Employee and Team

Jan