Splitting stats table based on columns from another table

[ALambert]

I have Table A that is a list of memberships and relevant info

constituent_id	membership_id	dues	program	category	subcategory	joined	expires	standing	date_added
187957	11019	50	DfPD	A. Community Member $50		09/15/21	10/04/23	Active	10/04/22
184600	9921	50	DfPD	A. Community Member $50		04/28/21	10/03/23	Active	10/04/22
158578	9402	45	DfPD	B. Individual Professional Member $45.00		08/21/20	10/03/23	Active	10/03/22
185639	9997	50	DfPD	A. Community Member $50		08/10/21	10/03/23	Active	10/03/22
189137	11748	100	DfPD	C. Individual Professional Member $100.00		10/03/22	10/03/23	Active	10/03/22
180325	9443	45	DfPD	B. Individual Professional Member $45.00		08/31/20	09/29/23	Active	09/29/22
189132	11747	50	DfPD	A. Community Member $50		09/29/22	09/29/23	Active	09/29/22
17058	11542	150	MMDG	04. Collaborating Friend		04/25/22	07/19/23	Active	07/19/22
177236	11657	3000	MMDG	08. Collaborating Partner		07/07/22	07/07/23	Active	07/07/22
178712	11655	150	MMDG	04. Collaborating Friend		07/06/22	07/06/23	Active	07/06/22
45601	11654	60	MMDG	03. Sponsoring Friend		07/06/22	07/06/23	Active	07/06/22
5221	9864	0	40th Anniversary	Season Pass		03/30/21		Dropped	11/05/21
10998	4965	40000	40th Anniversary	Season Sponsor		11/19/15	11/19/16	Lapsed	05/25/21
15105	9904	160	40th Anniversary	Season Pass		04/20/21	04/20/22	Lapsed	04/20/21

and Table B that is a date table with some statistics about membership activity for a given day

Date	Year	Month	Day	Active Members	Total Members
08/06/22	2022	8	6	1088	4860
08/07/22	2022	8	7	1083	4860
08/08/22	2022	8	8	1083	4860
08/09/22	2022	8	9	1086	4863
08/10/22	2022	8	10	1085	4863
08/11/22	2022	8	11	1082	4863
08/12/22	2022	8	12	1081	4863
08/13/22	2022	8	13	1080	4863
08/14/22	2022	8	14	1080	4863
08/15/22	2022	8	15	1080	4863
08/16/22	2022	8	16	1080	4863

the stats are calculated from Table A using

Active Members = COUNTROWS(FILTER('Constituent membership', AND('Date Stats'[Date] >= 'Constituent membership'[joined], 'Date Stats'[Date] <= 'Constituent membership'[expires])))

and

Total Members = COUNTROWS(FILTER('Constituent membership', 'Constituent membership'[joined] <= 'Date Stats'[Date]))

The issue I'm having is that if I create chart that uses stats from Table B, but then try to split it using the membership program as a legend, I just get 2 identical lines (the correct data is not identical)

Is there someway for me to create a relationship between these two tables so that I can get a line for each membership program without having to create a new measure for each program?

Any advice on what to lookup here or how to go about this is greatly appreciated. Not even sure what to google.

[ImkeF · Accepted solution]

Hi @ALambert ,
this looks like a typical "Events in progress"-pattern to me. You need either just one appropriate measure or a different helper table. Please check out this article: Events in progress – DAX Patterns and let me know if you have trouble implementing it to your case.