Conditional formatting in matrix comparing value with last column with data

[BrianJ]

@The_Bishop ,

Good news, bad news. The good news is that I think the measures all work exactly per your requirements now. This screenshot shows the zeros are now being handled properly:

The bad news is that I don’t understand why the previous measure didn’t work. Here’s the change I made:

Previously, this filter condition was expressed as [OverallAttend] <> BLANK()

To my mind, those statements should be exactly equivalent, but apparently they are not. The NOT(ISBLANK()) construct seems to treat blanks and zeroes as distinctly different results, while the <> BLANK() construct seems to treat blanks and zeroes as the same. Need to do more research on this, and probably make a video about what I find out.

But that’s my problem to figure out - the attached solution file should hopefully fully address your needs. Thanks for your patience - busy week, and a lot to unpack here. Fun problem, though.

• Brian
  eDNA Forum - DATASET Attendance Prev Cond Format Solution3.pbix (2.1 MB)

P.S. figured out the BLANK vs. ISBLANK issue. See post below for details:

DAX TIP: BLANK() vs ISBLANK()

All, I was recently working with @The_Bishop on a set of complex conditional formatting measures, and ran into a situation where we were getting some very unexpected results that after substantial effort traced back to the difference in how the DAX BLANK vs. ISBLANK functions treat zeros and blank/null values. I thought I would demo the three main cases to hopefully save you the same headache if you encounter a similar scenario. Here’s the Test data I created to illustrate the differences: […