Forum Discussion

paulvans182's avatar
paulvans182
Icon for Helper III rankHelper III
5 years ago
Solved

Count Employees with Multiple Interviews on Same Day

Good morning,

 

This is a bit of an extension of a previously answered question: DAX Count Records that have changed values

 

I have a data model that looks as follows:

 

The Scenario:

Employees have been interviewed twice over a period of time for several different Consultations (Working Hours, Pay Policy etc.).  I have noticed in the data that some employees have had both of their interviews on the same day for a particular survey.  I'd like to count the number of times this has happened.

 

I have tried to manipulate the solution I received in the above-referenced post.  Here is the formula: 

AOC Same Day Interviews = 
    SUMX(
        'Employees',
        VAR vEmployee = Employees[Employee ID]
        VAR vFirstInterview = 
            FILTER(ALLSELECTED('Interview_Fact'),
            Interview_Fact[EmployeeID]=vEmployee
            && Interview_Fact[Interview Details] = "First Interview"
            )
        VAR vSecondInterview = 
            FILTER(ALLSELECTED('Interview_Fact'),
            Interview_Fact[EmployeeID]=vEmployee
            && Interview_Fact[Interview Details] = "Second Interview"
            )
        VAR vFirstInterviewDate = MAXX(vFirstInterview,Interview_Fact[Date])
        VAR vSecondInterviewDate = MAXX(vSecondInterview,Interview_Fact[Date])
    RETURN
        IF(vSecondInterviewDate = vFirstInterviewDate,1,0)
    )

 

 

However, the results are completely wrong.  I believe the issue is because I need to add a third criterion to the vFirstInterview variable FILTER and the vSecondInterview variable FILTER to ensure that the two dates being compared are for the same Consultation ID.  However, I don't know how to include this within the FILTER variable.

 

Please could someone provide me some guidance on how to resolve this?  Thank you so much

 

Kind regards,


Paul

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    paulvans182 , Try like

    Check =
    var _tab = filter(Summarize(Interview_Fact,[EmployeeID],Interview_Fact[Date],"_1", distinctCOUNT(Interview_Fact[Interview Details]])),[_1] >1)
    return
    countx(summarize(_tab,[EmployeeID]),[EmployeeID])

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    paulvans182 , Try like

    Check =
    var _tab = filter(Summarize(Interview_Fact,[EmployeeID],Interview_Fact[Date],"_1", distinctCOUNT(Interview_Fact[Interview Details]])),[_1] >1)
    return
    countx(summarize(_tab,[EmployeeID]),[EmployeeID])

    • paulvans182's avatar
      paulvans182
      Icon for Helper III rankHelper III
      5 years ago

      Good morning amitchandak ,

       

      Thank you for the response, although it appears there is an error in the expression.  I tried to insert it and get the following issue:

       

      Is there perhaps a typo in your response?  I can't seem to spot the cause.

       

      Thanks again for the help, I really appreciate it. 

    • paulvans182's avatar
      paulvans182
      Icon for Helper III rankHelper III
      5 years ago

      Sorry, I am an idiot.  I didn't see the double square brackets after Employee Details.  I have corrected that and am testing out the solution.  Sorry, thank you.

    • paulvans182's avatar
      paulvans182
      Icon for Helper III rankHelper III
      5 years ago

      Hi amitchandak,

       

      Sorry, I have been validating the data and have spotted an issue.  

       

      Your expression appears to correctly identify whenever an Interviewer has interviewed an employee twice on the same day for a specific consultation. 

       

      However, when the Employee has been interviewed twice on the same day by a different Interviewer, this is not picked up.  You can see this in the image I have placed below

       

      Could you please help me to resolve this?

       

      Kind regards,

       

      Paul

      • paulvans182's avatar
        paulvans182
        Icon for Helper III rankHelper III
        5 years ago

        Hi amitchandak,

         

        I managed to fix it.  Removed the second SUMMARIZE that was contained in the COUNTX function and I am now getting the results I was after.

        Check = 
var _tab = 
    FILTER(
        SUMMARIZE(Interview_Fact,[EmployeeID],Interview_Fact[Date],"_1",COUNT(Interview_Fact[Interview Details])),[_1] >1)
RETURN
COUNTX(_tab,Interview_Fact[EmployeeID])

        Thanks again, sorry for spamming this thread so much - this problem has been driving me a bit crazy.

         

        Kind regards,

         

        Paul