Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Checking if employee is available

Hi guys,   I am trying to see if employees are sick, on holiday or available at the office. To do this I created two columns with "Status description" to determine if they are available or not   ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Really hard to do this one without sample data or more specifics around what you want this to end up like. But, let's say you have a data slicer that you filter down to a specific date. And you have a table visual of employees, you might be able to do a measure like:

 

Measure = 

  VAR __EmployeeID = MAX('Employees'[EmployeeID])

  VAR __Date = SELECTEDVALUE('Dates'[Date])

  VAR __LeaveStatus1 = MAXX(FILTER('LeaveTable1',[EmployeeID] = __EmployeeID && [Date] = __Date),[LeaveStatus1])

  VAR __LeaveStatus2 = MAXX(FILTER('LeaveTable2',[EmployeeID] = __EmployeeID && [Date] = __Date),[LeaveStatus2])

RETURN

<Implement your business logic here>

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg_DecklerThanks for helping me so far, but if I try your measure it will only return "Away" for one employee, not for the others.

 

Measure =
VAR _EmployeeID = MAX(Employees[ID])
VAR _AbsenceRegistration = MAXX(FILTER(AbsenceRegistrations; AbsenceRegistrations[Employee] = _EmployeeID); AbsenceRegistrations[ID])
VAR _Date = TODAY()
VAR _LeaveStatus1 = MAXX(FILTER(AbsenceRegistrationTransactions; AbsenceRegistrationTransactions[AbsenceRegistration] = _AbsenceRegistration && AbsenceRegistrationTransactions[StartDate] = _Date); [StatusDescription])
VAR _LeaveStatus2 = MAXX(FILTER(LeaveRegistrations; LeaveRegistrations[Employee] = _EmployeeID && LeaveRegistrations[StartDate] = _Date); LeaveRegistrations[StatusDescription])

return
IF(_LeaveStatus1 = "Away" ; "Away"; IF(_LeaveStatus2 = "Away"; "Away"; "Available"))
 
I added a small sample file, so maybe this helps Test.pbix