Total hours without employee duplicating

[jay_diehl]

Hi,

My datamodel is set up as followed for the dimensions:

Dim_project

ProjectID	Project
1	Test

 Dim_employee

EmployeeID	Name
1	Hans
2	Kees
3	Henk

Fact tables;

Fct_Forecast

ProjectID	EmployeeID	ForecastedHours
1	1	10

Fct_Costs

ProjectID	EmployeeID	WorkedHours
1	1	2
1	2	5
1	3	4

Now I want to achieve the following result on my dashboard:

Project	Name	Forecast	Total hours spent
Test	Hans	10	11

Because the employee is linked to costs table the value I get is the following:

Project	Name	Forecast	Total hours spent
Test	Hans	10	2

These are the hours that are booked by the employee Hans. I tried using all function as follows (Total hours spent = calculate(SUM('Fct_costs'[Workedhours]), All(Dim_employee[EmployeeID]))

but this resulted in the following table:

Project	Name	Forecast	Total hours spent
Test	Hans	10	11
Test	Kees		11
Test	Henk		11

How do I remove those rows for Kees and Henk and do I keep the employee name in my visual? I need to see who made the forecast. I first thought of making a measure to use as a flag when the forecast column is blank then 0 else 1 and then filter this value to be 1. This will not work because there are projects created by employees without a forecast. Anybody else has an idea how I can the employee name in the visual with the total hours for the project without the other employee names who booked the hours?

Thx

[tamerj1 · Accepted solution]

Hi @jay_diehl 

please try

spent =
IF (
NOT ISEMPTY ( Fct_Forecast ),
CALCULATE ( SUM ( 'Fct_costs'[Workedhours] ), ALL ( Dim_employee[EmployeeID] ) )
)