Hi,
My datamodel is set up as followed for the dimensions:
Dim_project
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
