Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dd88
Resolver I
Resolver I

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
 
PowerBI-Column-TestforBlanks-2a.png
 
PowerBI-Column-TestforBlanks-2b.png
 
 
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

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors