Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FrancisSaman
Frequent Visitor

ALLEXCEPT removes too much

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 modelData modelResultResult
1 REPLY 1
JustJan
Responsive Resident
Responsive Resident

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors