Forum Discussion
Splitting stats table based on columns from another table
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.
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.
1 Reply
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.