Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi members
Sorry, I'm a bit rusty on formulas (I'm just getting back into Powerbi after not using it for months).
I have this situation
Table Projects
ProjectName | Project Member | Hours |
Alpha | Marco | 20 |
Beta | Marco | 10 |
Gamma | Anna | 10 |
Gamma | Marco | 10 |
Table Timesheet
Day | Member | ProjectName | WorkedHours |
01/01/2021 | Marco | Alpha | 8 |
02/01/2021 | Marco | Alpha | 14 |
01/01/2021 | Marco | Gamma | 2 |
02/01/2021 | Marco | Beta | 4 |
So I builteda measure that tell me how many hours a Project Member had to work in a single Project
Project | Member | Hours | WorkedHours (Sum of Timesheet[WorkedHours]) |
Alpha | Marco | 20 | 22 |
Beta | Marco | 10 | 4 |
Gamma | Marco | 10 | 2 |
Gamma | Anna | 10 | 0 |
Now I want to know how may hours have to work Marco to complete asigned hours in his project.
The result is 14 (6 hours for project Beta and 8 hours for the project Gamma)
To build a measure I can't do a simple SUM Timesheet[WorkedHours] - Projects[Hours] because the result for the Alpha Project is -2
How can I exclude Rows where Timesheet[WorkedHours] > Projects[Hours] ?
Thanks
Marco
@marpisa , You need common project and memeber dimension and using that you can create measures like
sumX(filter(summarize(Timesheet, Project[ProjectName], Memeber[Project Member], "_1", SUM(Timesheet[WorkedHours]) - Sum(Projects[Hours])),[_1]>0),[_1])
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
11 | |
9 | |
9 | |
8 |