Count Employees with Multiple Interviews on Same Day

[paulvans182]

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

[amitchandak · Accepted solution]

@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]

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]

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

[paulvans182]

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]

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.