Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Switch statement - else text value

Hi,

Below Switch statement works for me but I need to include else then the result is a text value.

So

If 'powerbi114 cmQualifications'[qualificationId] = 3892 or 4036 or 4038 then

Expiry Date = "Does not Expire"

Anyway I can modify below Switch to include this?

Expiry Date =

SWITCH(

TRUE(),

'powerbi114 cmQualifications'[qualificationId] = 3892,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],3,YEAR),

'powerbi114 cmQualifications'[qualificationId] = 4036,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],2,YEAR),

'powerbi114 cmQualifications'[qualificationId] = 4038,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],3,YEAR))

Mariusz
6 years ago
Hi Anonymous

Sorry, my bad, all your conditions result in number and your else is text.

You will need to convert all the values you return to text using CONVERT() or FORMAT( 1, "") or return number in ELSE condition

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

4 Replies

Mariusz
Community Champion
6 years ago
Hi Anonymous

Else is the last argument like

Expiry Date = SWITCH( TRUE(), 'powerbi114 cmQualifications'[qualificationId] = 3892,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],3,YEAR), 'powerbi114 cmQualifications'[qualificationId] = 4036,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],2,YEAR), 'powerbi114 cmQualifications'[qualificationId] = 4038,DATEADD('powerbi114 cmQualifications'[awardDate].[Date],3,YEAR), "ELSE SOMTHING")

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn
- Anonymous
  Not applicable
  6 years ago
  Thanks for reply.
  I should have said I tried that but getting this error - Expressions that yield variant data-type cannot be used to define calculated columns.
  - Mariusz
    Community Champion
    6 years ago
    Hi Anonymous
    
    Sorry, my bad, all your conditions result in number and your else is text.
    
    You will need to convert all the values you return to text using CONVERT() or FORMAT( 1, "") or return number in ELSE condition
    
    Best Regards,
    Mariusz
    
    If this post helps, then please consider Accepting it as the solution.
    
    Please feel free to connect with me.
    LinkedIn

Related content