Total Trend Pressure DAX Measure

Overview of the Code Snippet

The code snippet represents a DAX (Data Analysis Expressions) formula intended to calculate a measure named Total Trend Pressure. It employs various DAX functions to aggregate and compute data based on specific filtering criteria from two tables: 'Scan Pest' and 'Service Order Attributes'.

Code Breakdown

1. Variable Definitions: The measure begins with the declaration of several variables, using the VAR keyword. Each variable performs distinct calculations to contribute to the final result.

2. LogicTest:

   VAR LogicTest =
       CALCULATE (
           COUNTROWS ( 'TimePeriodslicer' ),
           KEEPFILTERS ( 'TimePeriodslicer' )
       )

   • This variable counts the number of rows in the 'TimePeriodslicer' table, retaining any existing filter context. It serves as a preliminary check to determine if there is relevant data to work with.

3. OrdersWithCatches:

   VAR OrdersWithCatches =
       CALCULATETABLE (
           VALUES ( 'Scan Pest'[ServiceOrderKey] ),
           KEEPFILTERS ( 'Scan Pest'[CatchPestQuantity] > 0 ),
           ALLSELECTED ( 'Date' ),
           ALLSELECTED ( 'Date Translation' )
       )

   • This variable retrieves unique service order keys from the 'Scan Pest' table where the CatchPestQuantity is greater than zero, while removing filter restrictions on date-related tables.

4. OrderEquipmentWithCatches:

   VAR OrderEqiupmentWithCatches = 
       DISTINCT(
           SUMMARIZE(  
               CALCULATETABLE (
                   'Scan Pest',
                   KEEPFILTERS ( 'Scan Pest'[CatchPestQuantity] > 0 ),
                   ALLSELECTED ( 'Date' ),
                   ALLSELECTED ( 'Date Translation' )
               ),
               'Scan Pest'[ServiceOrderKey], 
               'Scan Pest'[CurrentEquipmentLocationDimKey]
           )
       )

   • This variable summarizes the data to get distinct combinations of service order keys and equipment location keys where CatchPestQuantity is greater than zero.

5. OrderEquipmentWithoutCatches:

   VAR OrderEquipmentWithoutCatches = 
       DISTINCT(
           SUMMARIZE(  
               CALCULATETABLE (
                   'Scan Pest',
                   KEEPFILTERS ( 'Scan Pest'[CatchPestQuantity] = 0 ),
                   ALLSELECTED ( 'Date' ),
                   ALLSELECTED ( 'Date Translation' )
               ),
               'Scan Pest'[ServiceOrderKey], 
               'Scan Pest'[CurrentEquipmentLocationDimKey]
           )
       )

   • Similarly to the previous variable, this one retrieves distinct combinations of service orders and equipment where CatchPestQuantity equals zero.

6. OrdersWithIsPestActivity:

   VAR OrdersWithIsPestActivity =
       CALCULATETABLE (
           VALUES ( 'Scan Status'[ServiceOrderKey] ),
           KEEPFILTERS ( 'Scan Pest'[PestQuantity] > 0 ),
           KEEPFILTERS ( 'Global Status'[IsPestActivity] = TRUE () ),
           ALLSELECTED ( 'Date' ),
           ALLSELECTED ( 'Date Translation' )
       )

   • Similar to earlier variables, this retrieves service orders related to active pest activity (non-zero PestQuantity).

7. OrderEquipmentWithIsPestActivity:

   VAR OrderEquipmentWithIsPestActivity = 
       DISTINCT(
           SUMMARIZE(  
               CALCULATETABLE (
                   'Scan Status',
                   KEEPFILTERS ( 'Scan Pest'[PestQuantity] > 0 ),
                   KEEPFILTERS ( 'Global Status'[IsPestActivity] = TRUE () ),
                   ALLSELECTED ( 'Date' ),
                   ALLSELECTED ( 'Date Translation' )
               ),
               'Scan Status'[ServiceOrderKey], 
               'Scan Status'[CurrentEquipmentLocationDimKey]
           )
       )

   • This variable focuses on summarizing data for equipment associated with pest activity.

8. Combining Results:

   VAR AllOrdersCatchesIsPestActivity = UNION(OrdersWithCatches, OrdersWithIsPestActivity)

   • Combines the previous results for orders with catches and those with pest activity.

9. CountCatches and TotalCountAlertEvents:

   VAR CountCatches =
       CALCULATE (
           [Total Catches],
           KEEPFILTERS ( 'Service Order Attributes'[ServiceOrderKey] IN ( OrdersWithCatches ) )
       )
   VAR TotalCountAlertEvents =
       CALCULATE (
           [Confirmed Alert Count],
           KEEPFILTERS ( 'Service Order Attributes'[ServiceOrderKey] IN ( AllOrdersCatchesIsPestActivity ) ),
           ALLSELECTED ( 'Date' ),
           ALLSELECTED ( 'Date Translation' )
       )

   • These variables calculate total catch counts and total alert events based on filters applied to the service order keys.

10. Final Calculations:

    VAR TotalCountPestActivity = ...
    VAR CountAlertEvents = ...
    VAR CountPestActivity = ...
    VAR Result = CountCatches +
        if( TotalCountAlertEvents >= TotalCountPestActivity, CountAlertEvents, 0) + 
        if( TotalCountPestActivity > TotalCountAlertEvents, CountPestActivity , 0)
        + 0
    RETURN
        IF ( LogicTest > 0, Result, BLANK () )

    • The calculations for pest activity counts, alert counts, and combining results lead to a final measure that returns Result if there are relevant time periods; otherwise, it returns BLANK.

Key Concepts Explained

• CALCULATE: A powerful DAX function that modifies the filter context to return the necessary values based on the provided conditions.
• KEEPOFILTERS: Maintains filters from existing contexts while allowing new modifiers.
• UNION: Allows for combining two or more tables, which is useful for categorizing data into a single dataset for computation.
• TREATAS: Applies values from one table to filter another, allowing for flexible context manipulation.

Additional Example

To further illustrate the concept of using filters and summarizing data, consider the following example that finds unique product keys with quantities greater than a defined threshold.

VAR ActiveProducts =
    CALCULATETABLE (
        VALUES ( 'Products'[ProductKey] ),
        'Products'[Quantity] > 10,
        ALLSELECTED ( 'Sales Date' )
    )

Conclusion

The Total Trend Pressure measure is a complex formulation that combines several filtering operations and logical evaluations to yield a meaningful aggregation of pest control activities. Through careful variable definitions and advanced DAX functions, the measure enables detailed analysis by leveraging the existing data model. For more in-depth learning on DAX functions and data modeling, consider exploring the courses available at the Enterprise DNA Platform.