Count =
// Date must be a real date, not a string.
var MinDate = MIN( T[Date] )
var CodeOfInterest = "SN23"
var CodesFromBefore = {"SN21", "MR28"}
var CasesOfInterest =
FILTER(
GROUPBY(
CALCULATETABLE(
// This SUMMARIZE finds the pairs of Case ID
// and Procedure Code in T where dates are
// before the min date visible and codes
// are filtered to be only the ones we're
// interested in.
SUMMARIZE(
T,
T[Case ID],
T[Procedure Code]
),
T[Date] < MinDate,
T[Procedure Code] in CodesFromBefore,
// This CALCULATETABLE returns only
// the Case ID's we're interested in
// from the current context.
CALCULATETABLE(
DISTINCT( T[Case ID] ),
KEEPFILTERS(
T[Procedure Code] = CodeOfInterest
)
)
),
// The GROUPBY takes the table from above and
// checks for each ID how many codes it
// has. If the number is 2, then we've got
// the CaseID we've been after. If it's one
// then one code is missing.
T[Case ID],
"@CodeCount", SUMX( CURRENTGROUP(), 1 )
),
// So now we are filtering out the case id's
// that have just one code.
[@CodeCount] = 2
)
var Result = COUNTROWS( CasesOfInterest )
RETURN
Result
