DAX Measure for Dept-wise Leave Count

This document outlines how to create a DAX measure that calculates the department-wise leave count based on a login table, considering the selected date range from a filter or slicer.

Table Structure

Assuming your login table has the following structure:

• EmpID: Employee Identifier
• EmpName: Employee Name
• Dept: Department Name
• LoginDatetime: Date and Time of Login
• Shift: Shift Information

Objective

The goal is to create a DAX measure that counts the leaves per department within a date range specified by slicers.

DAX Measure

Here’s the DAX code to calculate the department-wise leave count:

DeptWiseLeaveCount = 
VAR SelectedStartDate = MIN(DateTable[Date])  -- Get the minimum date from the DateTable
VAR SelectedEndDate = MAX(DateTable[Date])    -- Get the maximum date from the DateTable
RETURN 
    CALCULATE(
        COUNTROWS(LoginTable),  -- Count the number of logins (leaves) in the selected range
        LoginTable[LoginDatetime] < SelectedStartDate ||   -- Consider rows before start date
        LoginTable[LoginDatetime] > SelectedEndDate,      -- and after end date 
        VALUES(LoginTable[Dept])   -- Filter context will consider the department
    )

Explanation

1. Variable Initialization:

   • SelectedStartDate: Captures the earliest date selected in the slicer.
   • SelectedEndDate: Captures the latest date selected in the slicer.

2. CALCULATE Function:

   • COUNTROWS(LoginTable): Counts the number of rows in the LoginTable, which represents the leaves.
   • The conditions (LoginTable[LoginDatetime] < SelectedStartDate || LoginTable[LoginDatetime] > SelectedEndDate) ensure that only logins that fall outside the selected date range are included in the count.
   • VALUES(LoginTable[Dept]): Ensures that the measure respects the current filter context, so it calculates leave counts by department.

Input Validation

While DAX doesn't require explicit input validation like traditional programming languages, ensure that:

• The DateTable exists and is properly connected to your LoginTable.
• The Date slicer is correctly set up in your report.

Usage Example

To use this measure effectively in your Power BI reports:

1. Create a Date Table: Ensure you have a date table in your model and it is marked as a Date table.
2. Add Slicer: Include a slicer in your report using the DateTable[Date] column to allow users to select a date range.
3. Create a Table Visualization: Use a table visualization, place Dept on rows, and add the DeptWiseLeaveCount measure to the values field.

This setup will allow you to visualize the number of leaves taken per department based on the selected dates.

Conclusion

This DAX measure provides a scalable solution for analyzing leave counts within departmental contexts, accommodating any date range set by user interaction through slicers. For further enhancement of your skills in DAX and data modeling, consider exploring the Enterprise DNA Platform for high-quality learning resources.