Conditional formatting in matrix comparing value with last column with data

The_Bishop · August 20, 2021, 2:57pm

I hava matrix looking at course attendance at a college and the calculations are all working fine. I have been asked to do some conditional formatting based on the previous value (red is where attendance is lower than the last week of data, orange is the same, green is better than the previous attendance). Because students attend in different patterns across the year, the week commencing dates may not be consecutive:

You can see here that the value for 02/11/2020 is green because that particular course had no register marks in w/c 26/10/2020 (this is different to 0% attendance). The entry for 02/11 on the top line should be red when compared to 19/10, as it is lower than 19/10. The entry for 02/11 for the second line should be orange, as it is the same as 19/10. These gaps in the attendance for these courses is thrown out by the gaps in their attendance. 26/10 has a column because there are values further down the mtrix, of course.

I’ve searched on the forum and have tried to adapt a formula from @Greg but I can’t quite make it work. Additionally, the first week in the year always shows green because it is being regarded as better than some non-existent value, or rather, one I can’t see. I would want that to be #E7D6EA, which is the background colour to my table.

This is what I have at the moment:
Week Commencing Attendance Overall Colour =
VAR SelectedWeek =
SELECTEDVALUE ( Dates[Week Commencing] )
VAR PrevWeek =
CALCULATE (
MAX ( Dates[Week Commencing] ),
FILTER ( ALL ( Dates ), Dates[Week Commencing] < SelectedWeek
&& NOT(ISBLANK( Dates[Week Commencing])))
)

VAR SelectedWeekAttendance =
CALCULATE ( [Attendance Overall], Dates[Week Commencing] = SelectedWeek )
VAR PreviousWeekAttendance =
CALCULATE ( [Attendance Overall], Dates[Week Commencing] = PrevWeek )

RETURN
SWITCH (
TRUE (),
//ISBLANK(PreviousWeekAttendance), “#E7D6EA”,
SelectedWeekAttendance < PreviousWeekAttendance, “#D98880”,
SelectedWeekAttendance = PreviousWeekAttendance, “#FAE5D3”,
SelectedWeekAttendance > PreviousWeekAttendance, “#D4EFDF”,
BLANK()
)

My Dates table is related to the Attendance table (which carries all the attendance marks) via the Date to Session Date link.

Is anyone able to point me in the right direction? I have a lot of student and staff data in my .pbix, so randomising it or masking data would not be straightforward, so that would really be a last resort. I do appreciate that it would be useful to help solve this, though.

BrianJ · August 20, 2021, 3:45pm

@The_Bishop ,

I’m confident I can get this working for you if I had some masked data to play with, but I can’t do it just looking at some pretty extensive code and a partial data model screenshot. However, this may be easier to mask than you think:

Just include the tables and fields relevant to this problem
Just include a subset of rows big enough to be representative
Names are among the easiest things to mask. Here’s how I do it:

In PQ, add an index column to your dimension table (say, Students…). If the students are listed in alphabetical order, you may first want to sort the table on another column before adding the index. Then, once you’ve added the index, go to Add Column From Examples and call the column something like Masked Student Name. Now start entering examples on the first line - type “Student 1”. The algorithm should pick this up quickly and populate the entire column with Student 2, Student 3, etc. Then just delete the actual name column and you should be good to go.

Repeat as needed for professors, etc.

Brian

The_Bishop · August 21, 2021, 11:40am

Thanks @BrianJ . Please find attached a cut-down data model. This just includes my measures and the basic tables. There are now no student or staff names in the file. It is the Week Commencing Attendance Overall Colour meaure that is causing me issues. I just can’t quite get it to return no formatting for the first week of attendance (here, w/c 03/08/2020) and to have empty weeks for a course ignored back to the last week with data. I appreciate your reply.

The matrix is purely for testing the formatting measure and so won’t look as amateurish as this in production!
DATASET Attendance.pbix (2.1 MB)

BrianJ · August 21, 2021, 10:55pm

@The_Bishop ,

Okay, interesting problem. I think I’ve got it working properly, but please check to make sure it fully meets your requirements. The key was primarily to develop a virtual table variable that did all the necessary summarization and then filtered out the blank weeks so that the evaluation would “jump” back to last week with data. Below is the measure that does all the heavy lifting, followed by a much more simplified version of your conditional formatting measure. The key here is to add the blank condition upfront in the SWITCH/TRUE statement, otherwise it will evaluate blank as a zero value and assign a color other than the background color. (Note: when doing conditional formatting based on field values, assigning a value of BLANK() to the background color just maintains whatever is currently there).

Also, I changed the yellow color just for debugging purposes, since the color you had was difficult to differentiate from the pale pink. The yellow I used is ugly as sin on the pink background, but good for debugging.

Overall Attend PW = 

VAR vTable =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                ALL('Dates'),
                'Dates'[Academic Year],
                'Dates'[Week Commencing]
            ),
            "@TotAttend", [Total Actual Attends],
            "@OverallAttend", [Attendance Overall]
        ),
        [@OverallAttend] <> BLANK()
    )
    
VAR CurrAYr = SELECTEDVALUE( 'Dates'[Academic Year] )
VAR CurrWeek = SELECTEDVALUE( 'Dates'[Week Commencing] )

VAR PrevWeek =
    CALCULATE( 
        MAX( 'Dates'[Week Commencing] ),
        FILTER(
            vTable,
            'Dates'[Academic Year] = CurrAYr &&
            'Dates'[Week Commencing] < CurrWeek
        )
    )
    
    VAR PrevOverall =
    CALCULATE(
        [Attendance Overall],
        FILTER(
            ALL( Dates ),
            Dates[Academic Year]  = CurrAYr &&
            Dates[Week Commencing] =  PrevWeek
        )
    )

RETURN PrevOverall


CF Overall Attend = 

 SWITCH (
        TRUE (),
        [Attendance Overall] = BLANK(), BLANK(),
        [Attendance Overall] < [Overall Attend PW], "#D98880",
        [Attendance Overall] = [Overall Attend PW], "#FDD200",
        [Attendance Overall] > [Overall Attend PW], "#D4EFDF",
        "FFFFFF"
)

I hope this is helpful. Full solution file attached below.

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

The_Bishop · August 23, 2021, 9:11am

BrianJ:

CF Overall Attend = 

 SWITCH (
        TRUE (),
        [Attendance Overall] = BLANK(), BLANK(),
        [Attendance Overall] < [Overall Attend PW], "#D98880",
        [Attendance Overall] = [Overall Attend PW], "#FDD200",
        [Attendance Overall] > [Overall Attend PW], "#D4EFDF",
        "FFFFFF"
)

Thank you, @BrianJ . Your assistance is most appreciated. I’ve noticed that weeks with attenendance of 0% have no comparative formatting is applied i.e. it is the regular background colour, so it doesn’t seem to take into account the previous week’s value. Course HPTBENV2-2CXL31 is a good example:

If I move the BLANK() to the end of the SWITCH() statement (but before the default “FFFFFF”), I do get some formatting, but consecutive 0%s are treated as red (i.e. below the previous one) in the formatting.

Also, with the first week of attendance for each course (as you may have seen, some start at different times), do you know of a way I can have the default/blank formatting? The first week of the course’s attendance doesn’t have anything prior to compare to, so I would look to have it with no formatting/default colour.

I have tried to work this out myself, but am not having any joy at this point.

BrianJ · August 24, 2021, 5:48pm

@The_Bishop ,

Just wanted to let you know that I’m still working on this one. Been swamped at work, but hope to have some time tonight to work through the rest of the issues. The blank versus zero issue is proving more recalcitrant to resolve than I would’ve expected. Will get back to you soon hopefully with a full solution.

– Brian

The_Bishop · August 25, 2021, 7:36am

Oh, thank you so much @BrianJ . I’m very grateful for the time you have spent on this.

BrianJ · August 28, 2021, 7:03am

@The_Bishop ,

OK, I’ve got the first week issue solved. For debugging purposes, I’ve colored that week purple, but all you need to do is in the measure below, change the first condition of the SWITCH(TRUE) statement to return the hex code of the background color.

CF Overall Attend = 

 SWITCH (
        TRUE (),
        [Min Week Commencing] = [Harvest Week Commencing], "Purple",
        [Attendance Overall] = BLANK(), BLANK(),
        [Attendance Overall] < [Overall Attend PW], "#D98880",
        [Attendance Overall] = [Overall Attend PW], "#FDD200",
        [Attendance Overall] > [Overall Attend PW], "#D4EFDF",
        "FFFFFF"
    )

Still working on the zero vs. blank issue. Hope to have that cracked tomorrow.

Brian

eDNA Forum - DATASET Attendance Prev Cond Format Solution2.pbix (2.1 MB)

The_Bishop · August 28, 2021, 10:22am

Super! Thank you very much, @BrianJ

BrianJ · August 28, 2021, 4:52pm

@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:

The_Bishop · August 28, 2021, 8:42pm

@BrianJ I am so, so grateful for your perseverance on this one. I realised I had hit the limits of my DAX knowledge at this time, but to see someone of your standing having to grapple with it to get the right outcome made me feel slightly better. I am joking, of course, but I can only learn from this solution you’ve provided. This formatting was the finishing touch to a report I am working on in the final weeks of my time within my current organisation and, now, with this, this will be a fitting legacy to leave my colleagues with after I have moved on. With that in mind, I couldn’t be more appreciative of your help.

BrianJ · August 28, 2021, 8:47pm

@The_Bishop ,

Thanks for the backstory - so glad I was able to help you bring this to a successful close, and I learned a lot in the process as well. Best of luck in your future position, and if you run into any problems there, you know where to find us…

– Brian