Summing Data in a Virtual Table

[cdcarnes]

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.  Because the date range is a slicer I can't use a calculated collumn to determine eligibility.  I tried using a virtual table but I can't seem to get the count of services to work.  It will limit the list of clients counted to those that had at least 1 service but I can't get it to limit to those that had 2 services.  Any ideas on how to procede?  Here is the measure I am using right now.  The column for "CountOfServices" seems to be returning the same count for every client.  The measure returns the same value wether I have a filter on it or not. 

Eligible Population =

    VAR Tab1 =

        CALCULATETABLE(

            SELECTCOLUMNS(

                'dwh FactService',

                "ClientID",'dwh FactService'[clientID],

                "CountOfServices", COUNT('dwh 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"

        )

    VAR Tab2 =

        SUMMARIZE(

            tab1,

            [ClientID], [CountOfServices]

        )

    RETURN

        CALCULATE(

            COUNTROWS(

            Tab2),

            FILTER(

                tab2,

                [CountOfServices] > 1

            )

        )

[cdcarnes]

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

[Deku · Accepted solution]

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