How to deal with empty cells when using SWITCH

[andytmcc]

HI, 

I am creating a calculated column called 'LOC_conditionalFormat' that assigns a value based on two other columns. Here is my current code:

LOC_conditionalFormat = SWITCH(

TRUE(),

'Loneliness All Data'[Locus Of Control Mean] > 'Loneliness All Data'[LOC Previous Year],1,

'Loneliness All Data'[Locus Of Control Mean] < 'Loneliness All Data'[LOC Previous Year],2,

'Loneliness All Data'[Locus Of Control Mean] = 'Loneliness All Data'[LOC Previous Year],3,

0)

My problem is, if some data wasn't collected for a certain year then that causes a blank cell to be present in either 'Locus Of Control Mean' column or 'LOC Previous Year' column. The new column 'LOC_conditionalFormat' is then reading these blank cells as a zero and still assigning a number into the new column. i.e,  if 'Locus of Control' has a value of 15 and 'LOC Previous Year' is blank then a '1' will be placed in the 'LOC_conditionalFormat column as it is saying that 15 is greater than zero(blank). This '1' is then used for conditional formatting and applies a green up arrow to show that the value has increased.

I want to add something to my above code that says if LOC Previous Year column or Locus of Control mean column have a blank then to assign a different value (say '4). This '4' can then be used in the conditional formatting to apply a different icon, or no icon at all.

[andytmcc]

Thank you so much @PaulDBrown - exactly what I needed!

[PaulDBrown · Accepted solution]

Try:

LOC_conditionalFormat =
SWITCH (
    TRUE (),
    OR (
        ISBLANK ( 'Loneliness All Data'[Locus Of Control Mean] ),
        ISBLANK ( 'Loneliness All Data'[LOC Previous Year] )
    ), 4,
    'Loneliness All Data'[Locus Of Control Mean] > 'Loneliness All Data'[LOC Previous Year], 1,
    'Loneliness All Data'[Locus Of Control Mean] < 'Loneliness All Data'[LOC Previous Year], 2,
    'Loneliness All Data'[Locus Of Control Mean] = 'Loneliness All Data'[LOC Previous Year], 3,
    0
)

[andytmcc]

@PaulDBrown  Just wondering if you could help with one more addition to the solution you provided? How would I adjust your solution so that LOC_conditionalFormat didn't create a value for every row but only on rows where another column [Variable] contained the letters 'PC'?

[PaulDBrown]

Try:

LOC_conditionalFormat =
IF (
    CONTAINSSTRING ( MAX( Table[Variable] ) , "PC" ),
    SWITCH (
        TRUE (),
        OR (
            ISBLANK ( 'Loneliness All Data'[Locus Of Control Mean] ),
            ISBLANK ( 'Loneliness All Data'[LOC Previous Year] )
        ), 4,
        'Loneliness All Data'[Locus Of Control Mean] > 'Loneliness All Data'[LOC Previous Year], 1,
        'Loneliness All Data'[Locus Of Control Mean] < 'Loneliness All Data'[LOC Previous Year], 2,
        'Loneliness All Data'[Locus Of Control Mean] = 'Loneliness All Data'[LOC Previous Year], 3,
        0
    )
)

[andytmcc]

Hi @PaulDBrown , when I use your code it only creates empty cells. Not sure why! I will maybe repost this as a new question!

[PaulDBrown]

What is the actual syntax for the [Variable] you mentioned? Is it a measure or column? If it's a column, what is the syntax?

the MAX(Table[Variable]) was an assumption on my part, and is probably why you are getting blanks

[andytmcc]

Hi Paul, 

I was able to get this code to do what I wanted: 

LOC_conditionalFormat =

Var output = SWITCH (

TRUE (),

OR (

ISBLANK ( 'Loneliness All Data'[Locus Of Control Mean] ),

ISBLANK ( 'Loneliness All Data'[LOC Previous Year] )

), 4,

'Loneliness All Data'[Locus Of Control Mean] > 'Loneliness All Data'[LOC Previous Year], 1,

'Loneliness All Data'[Locus Of Control Mean] < 'Loneliness All Data'[LOC Previous Year], 2,

'Loneliness All Data'[Locus Of Control Mean] = 'Loneliness All Data'[LOC Previous Year], 3

)

RETURN
IF(All Data'[VARIABLE] IN {"PC", "LGD"}, output, BLANK())