switch

[Anonymous]

Hello,
I would like to add a column to an existing table in the model but the error is below. Any suggestions please?:

Function 'SWITCH' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

note that [CalendarQuarterKey] is int and holds values such as:
20153
20154
...
20201
...

StartMonthDayKey =
SWITCH(
'myDate'[CalendarQuarterKey],
1, LEFT('myDate'[CalendarQuarterKey], 4) & "0105"),
2, LEFT('myDate'[CalendarQuarterKey], 4) & "0405"),
3, LEFT('Date'[CalendarQuarterKey], 4) & "0705"),
4, LEFT('myDate'[CalendarQuarterKey], 4) & "1005")
)

[danextian]

Hi @Anonymous,

Text functions do not work on numbers. To be able to use a number as a text it has to be converted first to text using FORMAT function. For example:

Text to Number =
FORMAT ( 'Table'[NumberField], "#" )

In your case, you can store the text version of myDate'[CalendarQuarterKey] in a variable and use that in SWITCH instead. You also need to remove the extra closing parenthesis after each "01##" as they will cause a syntax error.

StartMonthDayKey =
VAR TextToNumber =
    FORMAT ( 'myDate'[CalendarQuarterKey], "#" )
RETURN
    SWITCH (
        'myDate'[CalendarQuarterKey],
        1, LEFT ( TextToNumber, 4 ) & "0105",
        2, LEFT ( TextToNumber, 4 ) & "0405",
        3, LEFT ( TextToNumber, 4 ) & "0705",
        4, LEFT ( TextToNumber, 4 ) & "1005"
    )

 Also

[Anonymous]

For the following DAX, the error is:
Function 'SWITCH' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

StartQuarterYearMonthDayKey =
VAR TextToNumber =
FORMAT ( left('Date'[Calendar Quarter Key], 4), "#" )
RETURN
SWITCH (
'Date'[CalendarQuarterKeyValue],
1, LEFT ( TextToNumber, 4 ) & "0105",
2, LEFT ( TextToNumber, 4 ) & "0405",
3, LEFT ( TextToNumber, 4 ) & "0705",
4, LEFT ( TextToNumber, 4 ) & "1005"
)

[az38 · Accepted solution]

Hi @Anonymous 

try CONVERT()

StartMonthDayKey =
var _CalendarQuarterKey = LEFT(CONVERT('myDate'[CalendarQuarterKey], STRING), 4)

RETURN 

SWITCH(
'myDate'[CalendarQuarterKey],
1, _CalendarQuarterKey  & "0105"),
2, _CalendarQuarterKey  & "0405"),
3, _CalendarQuarterKey  & "0705"),
4, _CalendarQuarterKey  & "1005")
)

[Anonymous]

, still getting error:

The syntax for ',' is incorrect. (DAX(var _CalendarQuarterKey = LEFT(CONVERT('Date'[Calendar Quarter Key], STRING), 4)RETURN SWITCH('Date'[CalendarQuarterKeyValue],1, _CalendarQuarterKey & "0105"),2, _CalendarQuarterKey & "0405"),3, _CalendarQuarterKey & "0705"),4, _CalendarQuarterKey & "1005")))).

[az38]

@Anonymous 

sorry, wrong parenthesys

StartMonthDayKey =
var _CalendarQuarterKey = LEFT(CONVERT('myDate'[CalendarQuarterKey], STRING), 4)

RETURN 

SWITCH(
'myDate'[CalendarQuarterKey],
1, _CalendarQuarterKey  & "0105",
2, _CalendarQuarterKey  & "0405",
3, _CalendarQuarterKey  & "0705",
4, _CalendarQuarterKey  & "1005"
)