Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

switch

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")
)

Need Help
  • az38's avatar
    az38
    6 years ago

    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")
)

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      , 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's avatar
        az38
        Community Champion
        6 years ago

        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"
)
  • danextian's avatar
    danextian
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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"
        )