The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I’m having trouble with figuring out what isnned to get the solution that i need.
I have three tables.
Clients
id | first_name | last_name | start_date | end_date | branch |
1 | Deborah | Hutton | 01/01/2021 | 01/02/2021 | a |
2 | Darrin | Whiterod | 02/01/2021 | {{Null}} | b |
3 | Madelene | Redemile | 03/01/2021 | 03/02/2021 | c |
4 | Roda | Merck | 04/01/2021 | {{Null}} | d |
5 | Bastien | Austwick | 05/01/2021 | 05/03/2021 | a |
6 | Gisele | Shankle | 06/01/2021 | 06/03/2021 | b |
7 | Sonnie | Jiroutka | 07/01/2021 | {{Null}} | c |
8 | Alta | Vedenyapin | 08/01/2021 | 28/01/2021 | d |
9 | Sarah | Habens | 09/01/2021 | 09/02/2021 | a |
10 | Torrey | Cragg | 10/01/2021 | {{Null}} | a |
Branch
id | branch_name |
1 | a |
2 | b |
3 | c |
4 | d |
Date Table
DateYearMonthnumber
01/01/2021 00:00 | 01/01/2021 |
02/01/2021 00:00 | 01/01/2021 |
03/01/2021 00:00 | 01/01/2021 |
04/01/2021 00:00 | 01/01/2021 |
05/01/2021 00:00 | 01/01/2021 |
06/01/2021 00:00 | 01/01/2021 |
07/01/2021 00:00 | 01/01/2021 |
08/01/2021 00:00 | 01/01/2021 |
09/01/2021 00:00 | 01/01/2021 |
Visits
client_id | visit_start | visit_end |
1 | 01/01/2021 10:30 | 01/01/2021 11:30 |
2 | 02/01/2021 08:00 | 02/01/2021 10:30 |
3 | 03/01/2021 06:00 | 03/01/2021 07:30 |
4 | 04/01/2021 10:00 | 04/01/2021 10:30 |
5 | 05/01/2021 01:00 | 05/01/2021 10:30 |
6 | 06/01/2021 10:15 | 06/01/2021 10:30 |
7 | 07/01/2021 23:00 | 08/01/2021 10:30 |
8 | 08/01/2021 05:00 | 08/01/2021 09:30 |
9 | 09/01/2021 02:00 | 09/01/2021 03:30 |
10 | 10/01/2021 07:00 | 11/01/2021 08:30 |
1 | 01/02/2021 10:00 | 01/02/2021 10:30 |
2 | 01/02/2021 10:00 | 01/02/2021 10:30 |
3 | 01/02/2021 10:00 | 01/02/2021 10:30 |
4 | 01/02/2021 10:00 | 01/02/2021 10:30 |
5 | 01/02/2021 10:00 | 01/02/2021 10:30 |
6 | 01/02/2021 10:00 | 01/02/2021 10:30 |
7 | 01/02/2021 10:00 | 01/02/2021 10:30 |
7 | 01/02/2021 14:00 | 01/02/2021 14:30 |
9 | 01/02/2021 10:00 | 01/02/2021 10:30 |
10 | 01/02/2021 10:00 | 01/02/2021 10:30 |
clients to branch is a one to many
visit to clients is one to many
both clients and visits are linked to dates one to many
I need to produce visualization that show the average number of hours for active clients at a branch for each given month, i know what the table that i would want to produce would/should look like but unshure how to reproduce it in power bi .
wanted table
Jan-21 | Feb-21 | |
a | 9.325 | 0.5 |
b | 1.225 | 0.5 |
c | 6.5 | 0.65 |
d | 2.5 | 0.3 |
Solved! Go to Solution.
@marcus_giddings , I think you need measure very similar to the current employee in this blog