Need a text column count in a matrix showing as a 0 not empty

[sr_cjs]

Afternoon, hoping someone can help me out here. Fairly new to using BI, and I'm trying to pull together a report for my safety team.

I have a table of safety visits, which includes lots of columns, but includes a DATE, A PERSON, and a TYPE ("Planned" or "Additional"). From the date, I also get the period that date falls within, and from the person I lookup a table of the departments.

I've pulled together the data in a matrix display; with QTR, PERIOD and TYPE being the columns, and DIR/DEPT and PERSON being the rows, and the values being the COUNT of Id. Which works well....I get columns for "Planned" and "Additional", and a count of each type.

However, I've also been asked to colour code the table - which I've started to do using a calculated measure (thanks to Google, Youtube, ChatGPT and forums) to work out the colour, and then applying that as a conditional format. I only want to format the values under "Planned", and only under certain conditions (Green if planned is over 0, orange if planned is 0 but additional is over 0, and red if both are 0). The issue is that when there are no "Planned" visits, it appears as an empty column in the matrix, which means I can't apply my formatting to it as there's no selection there.

FormattingPlannedCells = 
VAR CurrentPlanOrAdditional =
    IF (
        COUNTROWS ( VALUES ( SafetyRecords[PlannedOrAdditional] ) ) = 1,
        VALUES ( SafetyRecords[PlannedOrAdditional] ),
        "No Selection"
    )
RETURN
    IF (
        CurrentPlanOrAdditional = "Additional"
            || CurrentPlanOrAdditional = "No Selection"
            || CurrentPlanOrAdditional = "Unallocated"
            || CurrentPlanOrAdditional = BLANK (),
        BLANK (),
        IF (
            CALCULATE (
                COUNT ( SafetyRecords[ID] ),
                SafetyRecords[PlannedOrAdditional] = "Planned",
                VALUES ( SafetyRecords[Period] ),
                VALUES ( SafetyRecords[Person] )
            ) >= 1,
            "green",
            IF (
                OR (
                    CALCULATE (
                        COUNT ( SafetyRecords[ID] ),
                        SafetyRecords[PlannedOrAdditional] = "Planned",
                        VALUES ( SafetyRecords[Period] ),
                        VALUES ( SafetyRecords[Person] )
                    ) = 0,
                    ISBLANK (
                        CALCULATE (
                            COUNT ( SafetyRecords[ID] ),
                            SafetyRecords[PlannedOrAdditional] = "Planned",
                            VALUES ( SafetyRecords[Period] ),
                            VALUES ( SafetyRecords[Person] )
                        )
                    )
                )
                    && CALCULATE (
                        COUNT ( SafetyRecords[ID] ),
                        SafetyRecords[PlannedOrAdditional] = "Additional",
                        VALUES ( SafetyRecords[Period] ),
                        VALUES ( SafetyRecords[Person] )
                    ) > 0,
                "orange",
                "red"
            )
        )
    )

So, my query is, how can I get it to display a 0 (in the red oval in image above) as the count of Id, instead of a blank, so that I can get my formatting to apply and assign the colours of orange and red when "planned" is 0 and "additional" is either 0 or above.

Thanks for any help or pointers you can give.

[bharath_v]

Hi @sr_cjs 

Simple option would be to be make the source data "0" by replacing values. 

To do that:
1. Go to Power Query by going to "Transform Data". 

2. In the PQ editor, go to "Tranform" tab

3. Select the column (Planned in your case) and use "Replace values"

4. Insert 0 in Replace With and leave the Value to Find as it is (blank)

Now you should get 0 everywhere in the Planned column. Hope this is what you are after.

If I answered your question then pls mark my post as "Accept it as the solution" and a kudo would be appreciated.

[sr_cjs]

Hi @bharath_v , and thanks for your reply.

I've seen a few suggestions online to do that, and replace my blanks with "0" at source.

The issue here is that my source data's column "PlannedOrAdditional" is a column of text values; either "Planned" or "Additional", so there are no blanks to replace with "0" in the data

The "Planned" (and "Additional") columns are the possible values of that column when I add the "PlannedOrAdditional" field as a column to the matrix.

 

Chris