Forum Discussion

andytmcc's avatar
andytmcc
Icon for Helper I rankHelper I
3 years ago
Solved

How to deal with empty cells when using SWITCH

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.
 
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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
)

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      andytmcc
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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
    )
)