Summing Data in a Virtual Table

Question

I am having an issue.&nbsp;I have a report that needs to show a list of "eligible" clients.&nbsp; Eligiblity is determined by having 2 or more completed services in the date range that is selected.&nbsp; Because the date range is a slicer I can't use a calculated collumn to determine eligibility.&nbsp; I tried using a virtual table but I can't seem to get the count of services to work.&nbsp; 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.&nbsp; Any ideas on how to procede?&nbsp; Here is the measure I am using right now.&nbsp; The column for "CountOfServices" seems to be returning the same count for every client.&nbsp; The measure returns the same value wether I have a filter on it or not.&nbsp;&nbsp;&nbsp;Eligible Population = &nbsp; &nbsp; VAR Tab1 =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "ClientID",'dwh FactService'[clientID],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "CountOfServices", COUNT('dwh FactService'[ProcedureKey])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[ProcedureKey] in {99, 136, 60, 123, 124, 125, 126, 65, 66, 67, 68}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Full Service Date] &lt;= MAX('dwh DimDate'[SmallDate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Full Service Date] &gt;= MIN('dwh DimDate'[SmallDate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Age at Time Of Service] &gt; 17&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[ServiceStatusName] = "Complete"&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR Tab2 =&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tab1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ClientID], [CountOfServices]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Tab2),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tab2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [CountOfServices] &gt; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;&nbsp;

deku · Accepted Answer

Eligible Population =VAR Tab1 =CALCULATETABLE(Addcolumn(Values(dwh FactService'[clientID]),"@CountOfServices",Calculate( distinctcount(factService'[ProcedureKey])&nbsp;),'dwh FactService'[ProcedureKey] in {99, 136, 60, 123, 124, 125, 126, 65, 66, 67, 68}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Full Service Date] &lt;= MAX('dwh DimDate'[SmallDate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Full Service Date] &gt;= MIN('dwh DimDate'[SmallDate])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[Age at Time Of Service] &gt; 17&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'dwh FactService'[ServiceStatusName] = "Complete"&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;RETURNCOUNTROWS(&nbsp;FILTER(&nbsp; tab1,&nbsp; [@CountOfServices] &gt; 1&nbsp;))&nbsp;Assumed you wanted count of distinct procedure key, swap back to count if not&nbsp;You needed calculate to get context transition, so the row context of client id filtered the procedures&nbsp;

Forum Discussion

Summing Data in a Virtual Table

2 Replies

Related content

Fabric virtual table

Dax Logic Issue virtual Table

Dataverse Virtual table link to Fabric

Rolling Sum Using Virtual Table

GA availability for streaming connector virtual network