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 wit...
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" )
tamerj1
Community Champion
Community ChampionHi 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" )