Forum Discussion
Solved
Data Modeling for Manager-Supervisor-Employee Results Hierarchy
Good day! Below is an illustration of my tables and the desired result. Goal: To see performance results by Manager, Supervisor, Employee dynamically in different measures and different date ran...
Try this solution.
Create a table of distinct Employee ID in either Power Query or DAX. This is a bridge table between EmployeeHierarchy and each fact table.
Create measures:
Sales = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactSales[Date] ) VAR vResult = CALCULATE ( SUM ( FactSales[Sales] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultComplaints = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactComplaint[Date] ) VAR vResult = CALCULATE ( SUM ( FactComplaint[Complaints] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultSatisfaction Score = VAR vDate = TREATAS ( VALUES ( EmployeeHierarchy[Date] ), FactSatisfactionScore[Date] ) VAR vResult = CALCULATE ( MAX ( FactSatisfactionScore[Satisfaction Score] ), CROSSFILTER ( EmployeeHierarchy[Employee ID], DistinctEmployee[Employee ID], BOTH ), vDate ) RETURN vResultAdd EmployeeHierarchy fields and the measures to a visual:
This one works except if the employee ID is null. It happens from time to time that the datasource includes an employee that is not from this location. The results for that null employee gets assigned randomly to a supervisor/manager hierarchy for some reason. Any thoughts around that?
DataInsights
Super User
Super User
You could exclude null Employee IDs in Power Query (assuming you don't want these in your model). Or, you could replace null with a value like -1.