Forum Discussion
Solved
Calculated Column - Counting Duplicates in a Month
Hi Everyone,
I am trying to create a calculated column to flag the number of duplicates, where if the patient "[NHS_Number_Nat_Pseud]"
has called "[EMAS Call Connect Date]" more than 5 times within a month, I have created the following but to no success
EMAS Reattendance Month =
VAR Numberofcalls =
CALCULATE(
COUNT(EMAS[NHS_Number_Nat_Pseud]),FILTER
(ALL(EMAS),EMAS[NHS_Number_Nat_Pseud]= EARLIER(EMAS[NHS_Number_Nat_Pseud]) && month(EMAS[EMAS Call Connect Date])=
MONTH(EARLIER(EMAS[EMAS Call Connect Date])
&& YEAR(EMAS[EMAS Call Connect Date])=
YEAR(EARLIER(EMAS[EMAS Call Connect Date])
))))
RETURN
IF (Numberofcalls <=4 && NOT(ISBLANK(Numberofcalls)), " No", IF (ISBLANK(Numberofcalls),"", " Yes") )
Any help on this would be much appricated
Thanks
Nagin
Hi Nagin
please try
EMAS Reattendance Month = VAR CurrentMonth = MONTH ( EMAS[EMAS Call Connect Date] ) VAR CurrentYear = YEAR ( EMAS[EMAS Call Connect Date] ) VAR CurrentIDtable = CALCULATETABLE ( EMAS, ALLEXCEPT ( EMAS, EMAS[NHS_Number_Nat_Pseud] ) ) VAR FilteredTable = FILTER ( CurrentIDtable, EMAS[NHS_Number_Nat_Pseud] <> BLANK () && MONTH ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentMonth && YEAR ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentYear ) RETURN IF ( COUNTROWS ( FilteredTable ) > 5, "Yes", "No" )
3 Replies
- tamerj1
Community Champion
Hi Nagin
please try
EMAS Reattendance Month = VAR CurrentMonth = MONTH ( EMAS[EMAS Call Connect Date] ) VAR CurrentYear = YEAR ( EMAS[EMAS Call Connect Date] ) VAR CurrentIDtable = CALCULATETABLE ( EMAS, ALLEXCEPT ( EMAS, EMAS[NHS_Number_Nat_Pseud] ) ) VAR FilteredTable = FILTER ( CurrentIDtable, EMAS[NHS_Number_Nat_Pseud] <> BLANK () && MONTH ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentMonth && YEAR ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentYear ) RETURN IF ( COUNTROWS ( FilteredTable ) > 5, "Yes", "No" )