power bi function SWITCH does not suppport comparing values of type true/false. Consider using

[dd88]

power bi function SWITCH does not suppport comparing values of type true/false. Consider using the VALUE or FORMAT

The orginial condition was 

StatusColourNum = SWITCH('ActiveCIP'[Status], "Completed",1, "In progress & on schedule",2,"Yet to commence",3,"Behind schedule",4,"No status",5,"Not Progressing",6, "Archive",7, "(Blank)",8)

Then I realised the test for  (Blank) is ignored.  Although in Table View the data displays as (Blank).

StatusColourNum Format Type is Whole Number

I would like to accurately test for Blanks and if true action. I updated the condition to the following and now the error displays ..

power bi function SWITCH does not suppport comparing values of type true/false. Consider using the VALUE or FORMAT

StatusColourNum = SWITCH('ActiveCIP'[Status]=BLANK(),8, "Completed",1, "In progress & on schedule",2,"Yet to commence",3,"Behind schedule",4,"No status",5,"Not Progressing",6, "Archive",7)

How can this statement work?

TIA

[PhilipTreacy · Accepted solution]

Hi @dd88 

Try this

StatusColourNum = 

SWITCH(

    TRUE(),
    
    ISBLANK('ActiveCIP'[Status]),8,
    
    'ActiveCIP'[Status] = "Completed", 1,
    
    'ActiveCIP'[Status] = "In progress & on schedule", 2,
    
    'ActiveCIP'[Status] = "Yet to commence", 3,
    
    'ActiveCIP'[Status] = "Behind schedule", 4,
    
    'ActiveCIP'[Status] = "No status", 5,
    
    'ActiveCIP'[Status] = "Not Progressing", 6,
    
    'ActiveCIP'[Status] = "Archive", 7

    )

Alternatively, if all possible conditions (string values) of 'ActiveCIP'[Status] are listed in that code (values 1 through 7) then you can make the default result of the SWITCH be the result you want for BLANK values

StatusColourNum = 

SWITCH(

    TRUE(),
    
    'ActiveCIP'[Status] = "Completed", 1,
    
    'ActiveCIP'[Status] = "In progress & on schedule", 2,
    
    'ActiveCIP'[Status] = "Yet to commence", 3,
    
    'ActiveCIP'[Status] = "Behind schedule", 4,
    
    'ActiveCIP'[Status] = "No status", 5,
    
    'ActiveCIP'[Status] = "Not Progressing", 6,
    
    'ActiveCIP'[Status] = "Archive",7,

    8

    )

Regards

Phil

[dd88]

Many thanks  @PhilipTreacy  thats great. I used the 1st solution and it works!