Column chart with Joined Tables

[Anonymous]

Hi all,
I need to create column chart to show average age of children for users, grouped by user community.
I'm new to power bi and the problem is that dataset contains three tables here. I understand how to write sql query for the task but power bi way is not clear to me. I Tried to create measures to show average age but it just show me an average age for the whole children table.
So there are 3 tables.

Children
[Id][DueDate][CreatedDate][UserId(References User.Id)]

User
[Id][Email][CreatedDate]

UserProfile
[Id][CommunityId][UserId(References User.Id)]

So logically User and UserProfile are 1 to 1 but since it's a legacy db it's technically one to many. And I cannot change the relationship in Power BI since bad data exists (Some UserProfiles has UserId as null etc.).
A user may have a few children but I need only one that was added along with User (during registration).So I need to build a chart for the following sql query:

SELECT
u.CommunityEntryId, AVG(DATEDIFF(WEEK, ch.DueDate, u.CreatedDate)) as age
FROM Children ch
JOIN User u on ch.UserId = u.Id and ch.CreatedDate = u.CreatedDate
JOIN UserProfile up on up.UserId = u.Id
GROUP BY u.CommunityEntryId

Any help is greatly appreciated! Thanks!

[AllisonKennedy · Accepted solution]

@Anonymous  You can do this in Power BI with a merge in Power Query or with relationships. Power Query merge lets you do a join on two columns (UserID and CreatedDate) as you have done in your SQL query. 

Relationships can only be done on 1 column, so you'll need to create a new merged column (this can be done also in Power Query) in both the Children and the User table: Open the Children table in Power Query, select User column, hold Ctrl, Select CreatedDate column, in Add Column tab click Merge. Choose a unique separator (|, or whatever works for you). Repeat this same process in the User table.

Now you can create relationships in Power BI on Merged > Merged and UserID to ID. 

Then create a new measure: 

Age = AVERAGEX(Children, DATEDIFF(Children[CreatedDate], Children[DueDate], Week))

Then put in a visual with User[CommunityEntryId]