Forum Discussion

cdcarnes's avatar
cdcarnes
Frequent Visitor
1 year ago
Solved

Summing Data in a Virtual Table

I am having an issue.   I have a report that needs to show a list of "eligible" clients.  Eligiblity is determined by having 2 or more completed services in the date range that is selected.  Becaus...
  • Deku's avatar
    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