Internal and External turnover

[Atf23]

Hello,
I have 3 tables: Employee, Transactions and Date
in the table employee, we concatenate the monthly generated files where the unique field ID=employee_id+"_"+year(date)+"_"+month(date)

The table Transactions that is related to the Table Employee (1,*)

and there is a table Date related to the Employee Table (1,*)

And I have some slicers as follows:

Here is what I want do:
I want to display using CARDS while considering the selected values within the slicers:
** Number of Present Employees 
** Number of Absent Employees
** Number of left Employees (an employee is considered as left if he left internallly the selected departement or the direction or he left the company)

** Number of transactions realized by present employees 

** Number of transactions realized by Absent employees 
** Number of transactions realized by left employees 

Thanks of you help

[123abc · Accepted solution]

Here's a step-by-step guide:

1. Number of Present Employees:

Present Employees = COUNTROWS(FILTER(Employee, Employee[Status] = "Present"))

Number of Absent Employees:

Absent Employees = COUNTROWS(FILTER(Employee, Employee[Status] = "Absent"))

Number of Left Employees:

Left Employees =
CALCULATE(
COUNTROWS(FILTER(Employee, Employee[Status] = "Left")),
USERELATIONSHIP(Employee[DateID], Date[DateID])
)

Number of Transactions by Present Employees:

Transactions by Present Employees =
CALCULATE(
COUNTROWS(FILTER(Transactions, RELATED(Employee)[Status] = "Present")),
ALLSELECTED(Transactions)
)

Number of Transactions by Absent Employees:

Transactions by Absent Employees =
CALCULATE(
COUNTROWS(FILTER(Transactions, RELATED(Employee)[Status] = "Absent")),
ALLSELECTED(Transactions)
)

Number of Transactions by Left Employees:

Transactions by Left Employees =
CALCULATE(
COUNTROWS(FILTER(Transactions, RELATED(Employee)[Status] = "Left")),
ALLSELECTED(Transactions)
)

Make sure to replace Status with the actual field that indicates the employee's status (Present, Absent, Left). Also, adjust the relationships in your model accordingly.

After creating these measures, you can add them to your report and use the slicers to dynamically filter the results based on the selected values.

Remember to customize the DAX measures based on your actual data model and field names.