Dynamic DAX Measures to Generate Total Number of Rows / Records with Same/Duplicate Values

[LijunChen]

Dear All, 

I am analyzing a data with (children) client services records. The data 'service2018' listed the clients (cl_id) non-overlapping service episodes with service start and stop dates (start_x, stop_x). The data has also family id (fam_id) identifying whether the clients belong to the same family. 

I would like to create two Dax Measures:

a) total number of clients in service at any given date. 

b) total number of clients in any given date who has other family members (i.e., clients) in service at the same time. That is, total # of clients with 2 or more family members in service at the same date.

I have create date table 'Date_New'[Date] and linked its [Date] with service start date [start_x]. I would like to create a visual bar-chart of Total number of clients in service and number of clients with 2 or more family members across time (year/month). 

I have not problem with the 1st measure (clients in service at any date or beginning of date), which is as follows.

Measue_#inservice = CALCULATE (COUNTX (FILTER (service2018,service2018[start_x]<MIN('Date_New'[Date]) && service2018[stop_x]>=MIN('Date_New'[Date])),(service2018[cl_id])), CROSSFILTER(service2018[start_x],Date_New[Date],None))

But I cannnot figure out the correct DAX measure for the 2nd measure.

M_sibinservice = CALCULATE(COUNTX(FILTER(service2018,service2018[start_x]<MIN('Date_New'[Date]) && service2018[stop_x]>=MIN('Date_New'[Date]) && COUNTX(FILTER(service2018,EARLIER('service2018'[fam_id])='service2018'[fam_id]), 'service2018'[cl_id])>1), 'service2018'[cl_id]), CROSSFILTER(service[start_x],Date_New[Date],None))

In the above code, I use function EARLIER to generate the # of duplicates for each client/row, and filter in the rows with a value  greater than 1 together with the time filter. The measure can generate the summary values, but I don't think they are right because the final total count of clients with duplicate values are too many. I would be glad if I have your advice on how to solve this. Thanks.  

COUNTX(FILTER(service2018,EARLIER('service2018'[fam_id])='service2018'[fam_id]), 'service2018'[cl_id])>1 

[LijunChen]

Thanks for your reply. 

Here is an sample of the service records data that I have. 

cl_id	fam_id	episode_num	serv_seq	provider_id	start_x	stop_x	race5cat
1549222	20193350	2	1	59158	6/19/2013	3/13/2014	Black
1549222	20193350	2	2	15589	3/13/2014	4/23/2018	Black
1549222	20193350	2	3	98519	4/23/2018	5/2/2018	Black
1549222	20193350	2	4	15589	5/2/2018	6/1/2018	Black
2379187	22228166	7	1	241276	1/16/2019	8/20/2019	Black
1215291	22355646	5	1	248946	12/14/2017	12/18/2018	Other
6195703	22460997	4	2	514628	12/7/2019	12/19/2019	Other
5048033	22460997	1	1	257314	12/7/2019	1/22/2020	Other
2822470	22460997	2	2	257473	12/19/2019	1/15/2020	Other
3373233	22460997	2	2	257473	12/19/2019	3/12/2020	Other
2822470	22460997	2	3	118904	1/15/2020	1/23/2020	Other
5048033	22460997	1	2	257473	1/22/2020	3/31/2020	Other
2822470	22460997	2	4	257473	1/23/2020	3/31/2020	Other
3373233	22460997	2	3	118904	3/12/2020	3/19/2020	Other
3373233	22460997	2	4	257473	3/19/2020	3/31/2020	Other
2822470	22460997	2	5	257473	3/31/2020	6/17/2020	Other
8421266	22460997	3	8	514946	3/31/2020	7/9/2020	Other
2822470	22460997	2	6	231619	6/17/2020	6/23/2020	Other
2822470	22460997	2	7	257473	6/23/2020	7/9/2020	Other
11243736	22460997	5	18	532625	7/9/2020	7/13/2020	Other
3373233	22460997	2	7	257473	7/13/2020	7/24/2020	Other
7870503	22460997	3	14	514946	7/13/2020	9/10/2020	Other
3373233	22460997	2	8	251135	7/24/2020	9/16/2020	Other
7870503	22460997	3	16	32876	9/10/2020	10/7/2020	Other
3373233	22460997	2	9	245714	9/16/2020	10/15/2021	Other
2822470	22460997	2	11	258372	10/7/2020	2/22/2021	Other
5048033	22460997	1	7	258372	10/7/2020	8/6/2021	Other
2822470	22460997	2	12	640	2/22/2021	7/20/2021	Other
2822470	22460997	2	13	245714	7/20/2021	10/15/2021	Other
5048033	22460997	1	8	264659	8/6/2021	1/24/2022	Other
6195703	22460997	4	24	531930	10/15/2021	5/1/2023	Other
5048033	22460997	1	9	264659	1/24/2022	4/1/2022	Other
5048033	22460997	1	10	264659	4/1/2022	9/7/2022	Other
1513095	22501511	1	1	124546	9/1/2011	9/13/2011	Black
1513095	22501511	1	2	58521	9/13/2011	3/13/2017	Black
1513095	22501511	1	3	101460	3/13/2017	7/17/2018	Black
3916645	22565785	4	2	516782	3/5/2020	5/15/2020	Other
3916645	22565785	4	6	516782	8/13/2020	1/29/2021	Other
3916645	22565785	4	8	1316	1/29/2021	4/7/2021	Other
1011465	22622656	1	1	16438	9/8/2020	11/20/2020	White
6427775	22628097	1	1	267383	12/17/2022	3/7/2023	Other
6427775	22628097	1	3	265610	3/24/2023	5/1/2023	Other
1011479	22628097	1	1	265937	10/17/2021	1/10/2022	White
1011479	22628097	1	2	265937	1/10/2022	7/11/2022	White
16008608	22636235	3	3	726345	1/17/2023	1/27/2023	Black
16008608	22636235	3	6	813210	1/27/2023	5/1/2023	Black
2676799	22636235	1	1	108893	1/17/2023	1/27/2023	Other
2676799	22636235	1	2	271070	1/27/2023	5/1/2023	Other

What I would like to achieve is to create a dynamic measures to show (1) how many cl_id's are in service at any date based on 'start_x' and 'stop_x' (no overlapping service dates for any service record, but some are one follow the other); 2) how many cl_id's in service on any date have one other or more family members in service at the same time (based on family id 'fam_id'); and further, 3) how many of those with other family members in service at the same time have the same providers (provider_id).

Here is a screenshot of the visuals I created based on the first two measures I created with a date table linked to the service start date 'start_x'. But I don't think the numbers for the 2nd measure are corrected caculated. 

Your advice is appreciated. 

[v-zhangti]

Hi, @LijunChen 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LijunChen]

I have provided some sample data in the post above. Appreciate if you can help.