How to Replace Blanks with Zero (0) in Switch Formula with Text

[DMB90]

I have the following formula and I can't figure out how to make the blanks appear as zero "0".

Formula:

1.Q2 =

SWITCH(

    'Testing'[0.ACQuarters],

    "Q1 2025", "Q2 2025",

    "Q2 2025", "Q2 2025",

    BLANK())

[cengizhanarslan · Accepted solution]

1.Q2 =
VAR Result =
    SWITCH (
        'Testing'[0.ACQuarters],
        "Q1 2025", "Q2 2025",
        "Q2 2025", "Q2 2025"
    )
RETURN
IF ( ISBLANK ( Result ), "0", Result )

Set Data type = Text

[DMB90]

Thank you. This works.

[Cookistador]

Hi @DMB90 

The following dax measure should help you 

Q2 = 

COALESCE(

    SWITCH(

        'Testing'[0.ACQuarters],

        "Q1 2025", "Q2 2025",

        "Q2 2025", "Q2 2025"

    ), 

    0)

If you are using numerical field, replace the COALESCE() by a +0

[DMB90]

I now received the message "Expressions that yield variant data-type cannot be used to define calculated columns"

What should the Data Type be?

[MasonMA]

this will be valid for a calculated column to keep everything as text. 

Q2 =
COALESCE(
SWITCH(
'Testing'[0.ACQuarters],
"Q1 2025", "Q2 2025",
"Q2 2025", "Q2 2025",
BLANK()
),
"0"
)

[DMB90]

I now received the message "Expressions that yield variant data-type cannot be used to define calculated columns"

What should the Data Type be?

[Cookistador]

Sorry,  when I type the formula, I forgot the ""

Because you are working with textual value, and 0 is a numerical value, we have to convert it into string:

Q2 = 

COALESCE(

    SWITCH(

        'Testing'[0.ACQuarters],

        "Q1 2025", "Q2 2025",

        "Q2 2025", "Q2 2025"

    ), 

    "0")