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

 

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?
Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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'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
        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>