Forum Discussion
Anonymous
5 years agoNot applicable
DAX power query
Hi Super Users, I am trying to count the number of case ID's that have SN23 procedure code which went through SN21 or MR28 Procedure Code before. the data is as follows Case ID Procedure code ...
daxer-almighty
Solution Sage
5 years ago
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
Anonymous
5 years agoNot applicable
syntax was correct but it was not showing up the count. Instead resulting in BLANK