Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Date |
1505 | SN21 | May-19 |
1505 | SN21 | 10/5/2019 |
1505 | SN23 | 2/6/2020 |
1508 | SN21 | 4/7/2017 |
1508 | MR28 | 5/10/2014 |
1509 | SN21 | 10/10/2019 |
1509 | SN23 | 10/23/2019 |
1511 | SN23 | 2/5/2020 |
1512 | SN23 | 8/9/2020 |
1509 | MR28 | 2/6/2020 |
1513 | MR28 | 12/5/2020 |
1513 | SN23 | 1/5/2021 |
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
syntax was correct but it was not showing up the count. Instead resulting in BLANK
Hi @Anonymous ,
Use the below measure:
CountIds=
IF(AND(SELECTEDVALUE(Procedure code)= "SN23",OR(SELECTEDVALUE(Procedure code)= "SN21",SELECTEDVALUE(Procedure code)= "MR28")) ,CALCULATE(COUNT(Case Id)))
I hope this helps!
Kudos are always appreciated!
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |