DAX Optimization - CALCULATE, COUNTROWS, FILTER, RELATEDTABLE

Question

I have created the measure below to count equipment events over time.

It is possible for users to accidentally enter Events with dates before or after Equipment Commission and Decommission dates. In these instances, we need to exclude these Events. Event Date has a relationship with DateTable[Date].

It should be noted that each Equipment is assigned to a Facility. Prior to adding RELATEDTABLE, I could not use Facility as a category to get the result I need within visuals.

This measure works exactly as I require. However, I feel like it is terribly slow and inefficient.

Event Count =
  CALCULATE (
     COUNTROWS ( 'Events' ),
     FILTER (
       RELATEDTABLE( 'Equipment' ),
        ('Equipment'[CommissionDate] <= MAX ( DateTable[Date])
       || ISBLANK ( 'Equipment'[CommissionDate]))
       && ('Equipment'[DecommissionDate] >= MAX ( 'DateTable'[Date] )
       || ISBLANK ( 'Equipment'[DecommissionDate] ))
     )
   )

Any thoughts on improvements would be greatly beneficial.

optimization highly depends on your data model and the setting where the measure is evaluated. Therefore, you need to provide more information for example about cardinality and granularity of: (1) tables involved and (2) visualization where you want to use this measure. — Seymour

Seymour Seymour · Accepted Answer · 2020-04-05T16:01:10

As a rule of thumb, to speed-up DAX move calculations on the back-end. Therefore, ask yourself: do I really need to do this calculation dynamically in DAX or can it can be a static flag calculated on the back-end?

Therefore, I would create a flag in the back-end "EVENTS_TO_EXCLUDE_FL" such that the output is same as:

('Equipment'[CommissionDate] <= MAX ( DateTable[Date])
       || ISBLANK ( 'Equipment'[CommissionDate]))
       && ('Equipment'[DecommissionDate] >= MAX ( 'DateTable'[Date] )
       || ISBLANK ( 'Equipment'[DecommissionDate] ))
     )

Then, your measure become:

Event Count Optimized =
  CALCULATE (
     COUNTROWS ( 'Events' ),
     'Events'[EVENTS_TO_EXCLUDE_FL] = 0
   )

DAX Optimization - CALCULATE, COUNTROWS, FILTER, RELATEDTABLE

1 Answers