Counting through multiple tables

Question

Hello,&nbsp;&nbsp;I am managing data from a client database with Clients, Projects, and project interactions.&nbsp;&nbsp;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.&nbsp;There are around 15 projects, and the structure of the model is broadly as follows...&lt;--&gt; [bidirectional one to many ]--&gt; [one to many]&nbsp;[Client table] (demographic info) &lt;--&gt;(client id) [Project table 1] --&gt; (linked on project id) [project 1 project interactions]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&lt;--&gt;(client id) [Project table 2]&nbsp; --&gt;(linked on project id) [project 2 project interactions]&nbsp;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).&nbsp;&nbsp;What would be the best way to go about this?&nbsp;Thansk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

tex628 · Answer

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 · Answer

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

v-deddai1-msft · Answer

Hi Anonymous&nbsp;,&nbsp;Would you please show us some sample data. Please erase unnecessary data and sensitive data, we only need a part for testing.&nbsp;Best Regards,Dedmon Dai

anonymous · Answer

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&nbsp;&nbsp;See below comment for sample data&nbsp;

v-deddai1-msft · Answer

Hi Anonymous&nbsp;,&nbsp;The data you gave us is unreadable.&nbsp;Best Regards,Dedmon Dai

Forum Discussion

Counting through multiple tables

8 Replies

Related content

Counting nulls

Count across multiple columns

count values from multiple columns

Count from multiple columns

distinct count of multiple columns