Forum Discussion
Summing Data in a Virtual Table
- 1 year ago
Eligible Population =
VAR Tab1 =
CALCULATETABLE(
Addcolumn(
Values(dwh FactService'[clientID]),
"@CountOfServices",
Calculate( distinctcount(factService'[ProcedureKey])
),
'dwh FactService'[ProcedureKey] in {99, 136, 60, 123, 124, 125, 126, 65, 66, 67, 68}
&&
'dwh FactService'[Full Service Date] <= MAX('dwh DimDate'[SmallDate])
&&
'dwh FactService'[Full Service Date] >= MIN('dwh DimDate'[SmallDate])
&&
'dwh FactService'[Age at Time Of Service] > 17
&&
'dwh FactService'[ServiceStatusName] = "Complete"
)
RETURN
COUNTROWS(
FILTER(
tab1,
[@CountOfServices] > 1
)
)
Assumed you wanted count of distinct procedure key, swap back to count if not
You needed calculate to get context transition, so the row context of client id filtered the procedures
Thank you so much for the help.
I am looking for a total count of procedure key. Because the criteria is 2 or more completed in the list of procedure keys. But easy fix.
I actually tried to use calculate but it wasn't returning anything when I did. I am guessing using Addcollumns instead of Selectcollumns might have made a difference to.
Either way, it is working now.
Thanks again