Counting through multiple tables

[Anonymous]

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 the problems i'm struggling with is how to count all clients that have had a project interaction since "x" date.

There are around 15 projects, and the structure of the model is broadly as follows...

<--> [bidirectional one to many ]

--> [one to many]

[Client table] (demographic info) <-->(client id) [Project table 1] --> (linked on project id) [project 1 project interactions]

                                                   <-->(client id) [Project table 2]  -->(linked on project id) [project 2 project interactions]

What i need to do is create a measure that counts distinct client record ID if that client had an interaction with any project since a certain date (taken from project interaction table). 

What would be the best way to go about this?

Thansk 

[tex628]

Would it be possible for you to take a picture of the relationship pane in PBI? I'm having issues getting a clear picture of the structure.

/J

[Anonymous]

Yes - Sorry, didn't realise that I needed to sign in in order to do this...

Thanks 

[v-deddai1-msft]

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

[Anonymous]

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 

[v-deddai1-msft]

Hi @Anonymous ,

The data you gave us is unreadable.

Best Regards,

Dedmon Dai

[Anonymous]

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

[v-deddai1-msft]

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-oecS...

Best Regards,

Dedmon Dai

[Anonymous]

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