Forum Discussion

dd88's avatar
dd88
Icon for Post Patron rankPost Patron
2 years ago
Solved

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

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

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    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

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      dd88
      Icon for Post Patron rankPost Patron
      2 years ago

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