Exclude some value to Measure

marpisa — Thu, 17 Jun 2021 12:54:26 GMT

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

Re: Exclude some value to Measure

amitchandak — Fri, 18 Jun 2021 05:59:01 GMT

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])

topic Re: Exclude some value to Measure in DAX Commands and Tips

Exclude some value to Measure

Re: Exclude some value to Measure