Forum Discussion
Counting through multiple tables
Hi Anonymous ,
Would you please show us some sample data. Please erase unnecessary data and sensitive data, we only need a part for testing.
Best Regards,
Dedmon Dai
Thanks for your replies. I removed the bridge table 'distinct client record id' from the model. So please ignore that in the above screen shot. Here is some mock data: I'm trying to count distinct clients that have had a project interaction since X date. So in the example below. If I select dates as between 01/03/20 ad 01/05/20. then the result will be two clients - John and Mary. Ideally, the solution would allow me to alter the dates and see how many clients in that time easily. I don't know if i need to create an independent dates table for that also? Thanks
See below comment for sample data
Hi Anonymous ,
The data you gave us is unreadable.
Best Regards,
Dedmon Dai
Sorry-
Hope this works
Client table Client Record ID Client Name Client Age 1876 Steve 19 1563 John 26 1472 Mary 43 Elevate Table (project) Elevate Interactions Project Record ID Client Record ID Client Name Project Record ID Interaction Record ID Date of interaction 1888 1876 Steve 1888 223 15/05/2020 1654 1563 John 1654 245 07/04/2020 1888 125 01/05/2020 Advice and Wellbeing (Project) Advice and Wellbeing Interactions Project Record ID Client Record ID Client Name Project Record ID Interaction Record ID Date of interaction 1772 1472 Mary 1772 23 01/04/2020 1772 17 03/03/2019 Hi Anonymous
Sorry for late reponse. Please follow the steps:
1. Create a date table for slicer and create relationship between date table and interacions table(the relationship should be many to one):
Create a measure to filter the client table visual:
Measure = DISTINCTCOUNT('Advice and Wellbeing Interactions'[Date of interaction ])+ DISTINCTCOUNT('Elevate Interactions'[Date of interaction ])For more details, please refer to pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXqyYUAfaxVLv-oecS56pHABnVOGCm2e8jr5BMvv7jsajA?e=y8vV7m