Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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]
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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]
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |