Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 , 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])
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
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
85 | |
77 | |
66 |
User | Count |
---|---|
120 | |
111 | |
95 | |
83 | |
75 |