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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Greg_Deckler
Community 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