Sum up measure is not returning value

DAX DAX Calculations
1

I am creating a measure that sum up the count of events failures if count of event failure passes certain threshold value (let say 3) and failure event occured on the same location, same cause, and same equipment involved and happened within last N- months (let say 12). Below is my measure:

Card_measure =
Var _cause = MAX( ‘Sheet1’[Cause] )
Var _loc = MAX( ‘Sheet1’[CircuitID] )
Var _equip = MAX( ‘Sheet1’[Equipment] )
Var _zone = MAX(‘Sheet1’[ZoneSubstation])
Var _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value Value])
var _count = CALCULATE(DISTINCTCOUNT(‘Sheet1’[EventID]),FILTER(‘Sheet1’,‘Sheet1’[CircuitID] ==_loc && ‘Sheet1’[Cause] == _cause &&‘Sheet1’[ZoneSubstation] == _zone && ‘Sheet1’[Actual Interruption Start Time (NZST)]>=_mindate))
Return
Sumx(addcolumns(Summarize( ‘Sheet1’, [Cause],[CircuitID],[Equipment],[ZoneSubstation]), “_1”, IF(_count>[Trigger Value Value],_count) ), [_1])

However, the above measure returned blank when I used it in the card visual. I tried to debug the code and found that the _count variable returns null. I am not sure where I am making the mistake. Any help would be really appreciated.

Sample file here
sample_measure.pbix (37.9 KB)

2

@leo_89 ,

There are two problems with your measure:

  1. Your filter conditions produce the null set.

Here’s your data filtered for Cause =MAX(Sheet[Cause]):

image
image959×358 23.7 KB

At this point, none of the other filter conditions are true:

_loc = 255
_zone = “Willowbrook”
_miindate = 7/31/20

when in fact all three would need to be true in order to produce any values for the Count variable.

  1. Your IF statement is missing the third argument, which when not present defaults to Blank. Thus your statement is reading like this:

If Blank > 3 then Blank else Blank.

I hope this is helpful. Note that diagnosing these sorts of problems in complex measures is much easier when the measure is formatted.

  • Brian

P.S. I recently did the following video on debugging virtual tables. I think you’ll find it really useful, as these are the exact techniques I used for the analysis above.