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: E...
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])

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