Forum Discussion
Helper IIHow to Replace Blanks with Zero (0) in Switch Formula with Text
I have the following formula and I can't figure out how to make the blanks appear as zero "0".
Formula:
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
7 Replies
- Cookistador
Super User
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
DMB90I 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")
- 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"
)
- cengizhanarslan
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.
