Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Column chart with Joined Tables

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 underst...
  • AllisonKennedy's avatar
    5 years ago

    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]