Hi @frankhofmans ,
To achieve your desired functionality in Power BI, you need to establish the right relationships between the tables and create DAX measures to handle the filtering logic. The goal is to ensure that when you filter by an employee's name, you see both the tasks they executed (regardless of the case) and the tasks planned for cases under their responsibility. Additionally, when you select a case that the employee is responsible for, you should see all tasks executed in that case, even if they were completed by other employees.
First, let's focus on creating the correct relationships. In your data model, link the task executed table to the cases table using the case_id column. Similarly, link the task in future table to the cases table using the case_id column. Next, establish a relationship between the cases table and the employees table using the employee_id column, ensuring that each case is linked to the responsible employee. Finally, connect the task executed table to the employees table using the employee_id column to track which employee executed each task. With these relationships in place, your data model should be structured in a way that allows you to filter tasks by employee and case.
Once the relationships are set, you will need to create DAX measures to manage the filtering logic effectively. The first measure, Tasks Executed by Employee, ensures that when an employee is selected, you see all tasks they executed, regardless of the case. This measure can be created with the following DAX formula:
Tasks Executed by Employee =
CALCULATE(
COUNTROWS('task executed'),
FILTER(
'task executed',
'task executed'[employee_id] = SELECTEDVALUE('employees'[employee_id])
)
)
Next, you need a measure to show tasks planned for cases under the responsibility of the selected employee. This measure, Tasks Planned for Cases, can be written as follows:
Tasks Planned for Cases =
CALCULATE(
COUNTROWS('task in future'),
FILTER(
'task in future',
RELATED('cases'[employee_id]) = SELECTEDVALUE('employees'[employee_id])
)
)
Lastly, to ensure that selecting a case displays all tasks executed within that case, regardless of who executed them, you can create a measure called Tasks Executed in Selected Case. This measure uses the case_id from the selected case to filter the tasks executed:
Tasks Executed in Selected Case =
CALCULATE(
COUNTROWS('task executed'),
FILTER(
'task executed',
'task executed'[case_id] = SELECTEDVALUE('cases'[case_id])
)
)
With these measures created, you can set up your Power BI report visuals to reflect the desired behavior. Use a slicer for the employee_name column from the employees table to filter tasks by employee. In the report, one table visual can display the measure Tasks Executed by Employee to show tasks the selected employee has executed. Another table visual can show the measure Tasks Planned for Cases to reflect the planned tasks for cases under their responsibility. Finally, a table visual with the measure Tasks Executed in Selected Case will allow you to see all tasks executed in a selected case, regardless of who executed them. By setting up the relationships and measures in this way, you can achieve the desired functionality in your Power BI report.
Best regards,
