DAX Function to Count Shipments Above Target

Overview

The provided DAX expression attempts to calculate the count of shipments that meet specific weight criteria. However, it incorrectly uses a placeholder in a filter expression, which leads to an error. This guide will refactor the existing DAX code to avoid these issues and clarify the logic.

Key Components

1. Conditional Filtering: The DAX code aims to filter Fact Road Frieght based on weight ratios within specified bounds.
2. Aggregation: It calculates the number of rows that satisfy the filtering conditions.

Revised DAX Code

Here's a corrected version of the DAX expression to count the shipments above the target:

Count of Shipments Above Target = 
VAR SelectedLane = SELECTEDVALUE('Fact Road Frieght'[DIM_LANE_ID])
VAR AvgWeightFillRate = [Average Weight Fill Rate]
VAR ThirdQuartileWeightFillRate = [Third Quartile Weight Fill Rate]

RETURN
CALCULATE(
    COUNTROWS('Fact Road Frieght'),
    'Fact Road Frieght'[DIM_LANE_ID] = SelectedLane,
    'Fact Road Frieght'[MES_CHARGEABLE_WEIGHT] / 'Fact Road Frieght'[MES_EQUIP_MAX_WEIGHT_LBS] >= AvgWeightFillRate,
    'Fact Road Frieght'[MES_CHARGEABLE_WEIGHT] / 'Fact Road Frieght'[MES_EQUIP_MAX_WEIGHT_LBS] <= ThirdQuartileWeightFillRate
)

Explanation of the Code

1. Variables Declaration:

   • SelectedLane: Captures the currently selected lane ID to avoid duplicate calculations.
   • AvgWeightFillRate: Stores the average weight fill rate for reuse.
   • ThirdQuartileWeightFillRate: Stores the third quartile weight fill rate for reuse.

2. CALCULATE Function:

   • It modifies the filter context for the counting of rows in the Fact Road Frieght table.
   • Filters based on lane ID, ensuring that the two weight ratio conditions are satisfied.

Input Validation

• Ensure that [Average Weight Fill Rate] and [Third Quartile Weight Fill Rate] are valid numerical values.
• Check that the DIM_LANE_ID has appropriate selections made in the context.

Example Usage

You can use this measure in a Power BI report to dynamically count the shipments for a selected lane that fall within the calculated weight thresholds.

Scenario

• If a user selects Lane A in a slicer, this measure will return the count of shipments for Lane A where the MES_CHARGEABLE_WEIGHT divided by MES_EQUIP_MAX_WEIGHT_LBS falls between the specified average and third quartile weight fill rates.

Conclusion

This revised DAX measure effectively resolves the issues present in the original expression while adhering to best practices in structure and readability. For more advanced DAX techniques, consider exploring resources on the Enterprise DNA Platform.