Checking if employee is available

[Anonymous]

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

Column 1:

StatusDescription = IF(AND(LeaveRegistrations[StartDate] <= TODAY(); LeaveRegistrations[EndDate] >= TODAY()); "Away"; "Available")

Column 2:

StatusDescription = IF(AbsenceRegistrationTransactions[StartDate] >= TODAY()-7; IF(AbsenceRegistrationTransactions[Status] = 1; "Available" ; IF(AbsenceRegistrationTransactions[Status] = 0; "Away"; "Unknown")); IF(AbsenceRegistrationTransactions[Status] = 1; "Available"; "Unknown"))

So now I want to combine these two to see if someone is away or not, but I can't get this to work.

Below is a screenshot of the needed tables and their connections. Can anyone help?

[Greg_Deckler]

So, what takes precedence? If one column shows Available and one Away, which is it and what is it for all possible combinations of values?

[Anonymous]

@Greg_Deckler  If they are either sick or on holiday they should be away.

So it should look something like:

Measure x = IF('AbsenceRegistrationTransaction'[StatusDescription] = "Away" ; "Away"; IF('LeaveRegistration'[StatusDescription] = "Away"; "Away"; "Available"))

[Greg_Deckler]

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]

@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