Forum Discussion
Counting through multiple tables
Hello, I am managing data from a client database with Clients, Projects, and project interactions. I have re-created this within Power BI and am now trying to calculate a few things. One of...
Sorry-
Hope this works
| Client table | ||||||||
| Client Record ID | Client Name | Client Age | ||||||
| 1876 | Steve | 19 | ||||||
| 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
Thank very much,
I had thought that this would be the solution. Another alternative I thought would be to combine everything in one big table.
I'll try by creating a linked date table.
THanks