DAX Formula Creation for Total_CS_PY

Formula

Total_CS_PY = 
CALCULATE(
    COUNT(Crew_Absent_FACT[DUTY_CODE]),
    Codes_Dim[Category] = "sickness"
)

Explanation

1. CALCULATE Function:

   • The CALCULATE function is used in DAX to modify the filter context of the data being evaluated for numerical calculations. It allows for a potential change in the filtering conditions applied to the data set.

2. COUNT Function:

   • The COUNT function counts the number of rows in a specified column that contain numeric values. In this case, it counts the number of duty codes in the Crew_Absent_FACT table.

3. Filtering Condition:

   • The expression modifies the filter itself by applying a condition. Here, it filters to include only those entries in Codes_Dim where the Category is equal to "sickness".

Logic Flow

• The formula calculates the total count of DUTY_CODE entries from the Crew_Absent_FACT table.
• It only considers the rows where the corresponding Category in Codes_Dim is "sickness".
• As a result, Total_CS_PY reflects the count of duty codes related specifically to sickness absences.

Practical Illustration

Scenario

Consider a dataset with two tables:

• Crew_Absent_FACT: Contains records for crew absence with the column DUTY_CODE.
• Codes_Dim: Provides details about the absence category with a column Category.

Example Data

Crew_Absent_FACT Table

Codes_Dim Table

Execution

When the formula executes:

• It checks the Codes_Dim table for entries where Category is "sickness".
• Based on the available data, suppose three entries in Crew_Absent_FACT correspond to the "sickness" category in Codes_Dim.

The output for Total_CS_PY will thus be the count of those occurrences, effectively giving the total number of duty codes associated with sickness.

User Understanding

By using this DAX formula, users can quickly tally crew absences categorized under sickness, offering insights into absenteeism trends within their datasets. For enhanced DAX comprehension and practical applications, consider exploring resources available on the Enterprise DNA Platform.