Counts based on data from two unconnected tables

[wintergreen13]

Apologies if this is a well-known issue - I'm not sure what to call it or even how to describe my problem in a brief way, so I wasn't able to find any quick YouTube explanations. Here's my setup:

I have three tables. Table 1 houses basic demographic data: gender, DOB, the like, with a key ID field. Table 2 houses family composition data: family name, relationship, etc. The relationship between table 1 and 2 is one to many (each person can be in more than one family) based on ID. Table 3 has activity data and tracks when the head of household attended an event. The relationship between table 1 and 3 is also one to many (each person can attend many events) based on ID. There is no relationship between tables 2 and 3.

I have been asked to count demographic values, gender for instance, of all of the family members of people who attended events during a period of time. It's easy enough to count this for the head of household, since a row of data is recorded for them each time they attend. But how do I count the other family members who do not have any meaningful tie to the attendance except through the unconnected family composition table? If anyone has any experience with this issue I would love to hear how you dealt with it.

[v-kpoloju-msft · Accepted solution]

Hi @wintergreen13, 

Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @wintergreen13,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @wintergreen13,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @wintergreen13,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...