Blogs

Power BI > Conditional Formatting the Table > Use Case

Inkey, December 13, 2018 3727 Views

Microsoft has released a very exciting feature – Conditional formatting by Field Value. Hence, we plan to explain how we took it’s best use with an example.

There are times when we want to conditionally format a field that returns both text values and decimal numbers. To understand this better, let’s take some sample data having the statuses of teachers during a month.

Here, each teacher has 3 of the mentioned statuses, i.e. , Reserved, Free and Off, for a particular day.

•  Off depicts that the particular teacher has taken leave for that particular day.
•  Reserved depicts that the particular teacher has it’s schedule ready and how much he/she has worked for the day which is calculated in the field ‘Working Hours’.
•  Free depicts that the particular teacher doesn’t have any schedule for that day and is available for any work.

Now suppose, the user wants to see which teacher has worked for how many hours on a particular day, how much she remains free and how many leaves has she taken. But do not want statuses to be displayed, instead want to indicate statuses(i.e Reserved, Free and Off) with particular colors.

For instance,

Leaves – Yellow Color,
Free Time – Blue Color
If they are reserved, show their working hours for that particular day and
If there is no data for that particular teacher and day, that means the schedule is yet to be prepared and it should be shown in – Red Color

To achieve this, we used the new feature included , i.e. Conditional formatting by field value.
We created the following measure as per the feature that will be plotted in the matrix.

StatusColor = 
VAR teacherStat =
    FIRSTNONBLANK ( TeacherStatus[Status], TeacherStatus[Status] )
RETURN
    IF (
        teacherStat = "Off",
        "#F7DE6F", //yellow
        IF (
            teacherStat = "Reserved", 
            "#FFFFFF" //white
            IF (
                teacherStat = "Free",
                "#67D4CC",//blue
                IF ( ISBLANK ( teacherStat ), "#FD625E" //red)
            )
        )
    )
)

And then set conditional formatting on this particular measure for background color and font color as per field value as follows:

On plotting the following measure in matrix, I get the following result:

Now, instead of white color, we wanted to see working hours for that particular teacher.
Hence, we made a small change for the ‘Reserved’ status in the measure as follows:

StatusColor = 
VAR teacherStat =
    FIRSTNONBLANK ( TeacherStatus[Status], TeacherStatus[Status] )
RETURN
    IF (
        teacherStat = "Off",
        "#F7DE6F", //yellow
        IF (
            teacherStat = "Reserved", 
            SUM(TeacherStatus[Working Hours]),
            IF (
                teacherStat = "Free",
                "#67D4CC",//blue
                IF ( ISBLANK ( teacherStat ), "#FD625E" //red)
            )
        )
    )
)

and below is what we see :

To get the WHITE background color for Reserved status, we need to set the default background color to White.

Now the problem is, we see the total as the code of a color but the requirement was to show totals as Total of working hours and if the teacher hasn’t worked, it should show 0 in the total.

So the feature “Conditional Formatting using field values” is no longer useful to meet the expected results.
Hence, we thought of a workaround where we can show total of working hours. We created another measure to get the expected result.

Measure is as follows:

FinalStatus = 
VAR totalWorkingHours =
    SUM ( TeacherStatus[Working Hours] )
RETURN
    IF (
        NOT ( ISBLANK ( totalWorkingHours ) ),
        totalWorkingHours,
        IF (
            FIRSTNONBLANK ( TeacherStatus[Status], 1 ) = "Off",
            2,
            IF (
                FIRSTNONBLANK ( TeacherStatus[Status], 1 ) = "Free",
                3,
                IF ( ISBLANK ( totalWorkingHours ), 4 )
            )
        )
    )

Here, we assigned numbers for each status so that we can use the feature “Conditional formatting using Rules”.

Next we plotted the above measure in the matrix and gave conditional formatting for background color and font color as follows:

And we got the below result:

But here we can see that where there wasn’t any teacher working, we are not getting totals as 0(zero) but something else.
Hence, we thought of another logic to show totals as 0(zero) and made the below changes to measure as follows:

FinalStatus = 
VAR totalWorkingHours =
    SUM ( TeacherStatus[Working Hours] )
RETURN
    IF (
        NOT ( ISBLANK ( totalWorkingHours ) ),
        totalWorkingHours,
        IF (
            FIRSTNONBLANK ( TeacherStatus[Status], 1 ) = "Off",
            0.000002,
            IF (
                FIRSTNONBLANK ( TeacherStatus[Status], 1 ) = "Free",
                0.000003,
                IF ( ISBLANK ( totalWorkingHours ), 0.000004 )
            )
        )
    )

We have just converted whole numbers into decimal numbers, and then given conditional formatting as follows:

And voila! We got the expected result,

Hence, with some workarounds we could get the expected result for the totals of the blank values if conditional formatting is given to it.

Happy DAX-ting!