Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Internal and External turnover

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) ...
  • 123abc's avatar
    123abc
    2 years ago

    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.

     
     
     
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.