Function 'SWITCH' does not support comparing values of type Text with values of type True/False

[Clikojo]

Hello all, I am building a funnel chart. I am trying to have an overall/generic showing when there is no slicer selection and poduct specific categorys when there is a selection.

Here is my DAX:

DynamicStateGroup =

IF (

    ISFILTERED('ProductTable'[Product]),  -- Check if something is selected in the slicer

    'stg_product_onboarding'[updated_state],  -- If something is selected, return the original state

    SWITCH(

        'stg_product_onboarding'[updated_state],  -- When no slicer selection, group states

        "Onboarding Success", "Onboarding Success",

        'stg_product_onboarding'[updated_state] IN {"contract_1, "contract_2"}, "Sign Contracts",

        'stg_product_onboarding'[updated_state]

    )

)

However, I keep recieving this error
Function 'SWITCH' does not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

Thank you in advance!

[Bibiano_Geraldo · Accepted solution]

Hi @Clikojo ,

It looks like the issue is with the way the SWITCH function is being used. The error occurs because SWITCH is trying to compare a text value with a boolean value. To fix this, you can use the SWITCH(TRUE(), ...) pattern, which allows for more flexible comparisons.

Here’s how you can modify your DAX formula:

DynamicStateGroup =
IF (
    ISFILTERED('ProductTable'[Product]),  -- Check if something is selected in the slicer
    'stg_product_onboarding'[updated_state],  -- If something is selected, return the original state
    SWITCH(
        TRUE(),
        'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
        'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
        'stg_product_onboarding'[updated_state]
    )
)

Give this a try and let me know if it works for your funnel chart! If yes, please consider to accept as solution and give a Kudo. 

You can lear more about SWITCH function here 

Thank you.

[Clikojo]

@Bibiano_Geraldo Thank you for your help. The issue I am still having is that the count changes when a selection is made but the funnel visual does not. With no slicer selection it shows the grouped categorys and with a selection the same thing but just with the correct count. I am entering this DAX as a new column within my table. This is the correct approach right?

[Bibiano_Geraldo]

Hi @Clikojo ,
To achieve dynamic behavior based on slicer selection, you should use a measure instead of a calculated column. Measures recalculate dynamically based on the filters and slicers applied in your report.

Here's revised DAX measure:

DynamicStateGroupMeasure =
IF (
    ISFILTERED('ProductTable'[Product]),  -- Check if something is selected in the slicer
    MAX('stg_product_onboarding'[updated_state]),  -- If something is selected, return the original state
    SWITCH(
        TRUE(),
        MAX('stg_product_onboarding'[updated_state]) = "Onboarding Success", "Onboarding Success",
        MAX('stg_product_onboarding'[updated_state]) IN {"contract_1", "contract_2"}, "Sign Contracts",
        MAX('stg_product_onboarding'[updated_state])
    )
)

Now you can use this measure in the funnel chart as your dynamic state field.

I hope this helps! 🎯
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
👍 Give it a kudo to show your appreciation!

[Greg_Deckler]

@Clikojo You need to use the SWITCH( TRUE(), ... ) version like this:

IF (
    ISFILTERED('ProductTable'[Product]),  -- Check if something is selected in the slicer
    'stg_product_onboarding'[updated_state],  -- If something is selected, return the original state
    SWITCH( TRUE(), 
        'stg_product_onboarding'[updated_state] IN {"contract_1, "contract_2"}, "Sign Contracts",
        'stg_product_onboarding'[updated_state]
    )
)

[Clikojo]

Thanks @Greg_Deckler, that seemed to resolve my error however, my funnel chart is not behaving as expected. These grouped categorys remain regardless if I have a selection on my slicer or not. Are you able to give some pointers as this might be happening please?

[SenPower]

@Clikojo are there different tables you have used to built the funnel data, if so than it might be the relationship problem.

[Clikojo]

@SenPower I am using the same table 'stg_product_onboarding' for both the slicer and funnel. I use producttype for the slicer and trying to use dynamicstategroup in category for funnel visual

[SenPower]

@Clikojo Can you modify your measure with this 
DynamicStateGroup =
IF (
HASONEVALUE('ProductTable'[Product]),
VALUES('stg_product_onboarding'[updated_state]),
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state]
)
)

[Clikojo]

@SenPower Thank you for your help. The issue I am still having is that the count changes when a selection is made but the funnel visual does not. With no slicer selection it shows the grouped categorys and with a selection the same thing but just with the correct count. I am entering this DAX as a new column within my table. This is the correct approach right?

[SenPower]

Ahhh, i undersrtand now, it seems like you have created a calculated column rather than a measure. Please create a measure not the column using the below DAX and is the table you are using as STG_Product_Onboarding check and see if that table depends on other tables if so than check the relationships of the table. 

But test it with the below DAX as a mesure not the calculated column. 
DynamicStateGroup =
IF (
ISFILTERED('ProductTable'[Product]), 
SELECTEDVALUE('stg_product_onboarding'[updated_state]), 
SWITCH(
TRUE(),
'stg_product_onboarding'[updated_state] = "Onboarding Success", "Onboarding Success",
'stg_product_onboarding'[updated_state] IN {"contract_1", "contract_2"}, "Sign Contracts",
'stg_product_onboarding'[updated_state] 
)
)