Replace Calculated Measure With "New" When Year over Year = -100 (SQL Case When)

[ehanson2]

Hello, I am new to PowerBI and learning. I created calculated measures for Product Revenue TY and PR LY, with one more for YoY PR. In my YoY PR column, I have a bunch of -100% because there are some new products in the table this year. I'm wondering how I can simply replace the -100%'s with "New" to make it look cleaner. I know in sql you can use a case when statement to replace the values, i'm lost on how to do it here. 

Please note these are calculated measures, not tables.

Calculations: 

Product Revenue This Year: 

PRTY =

VAR

CurrentDate = [Date Reference]

RETURN

CALCULATE(SUM(Dashboards_Merch[Product Revenue]),Dashboards_Merch[Order_Year] = YEAR(CurrentDate))

Product Revenue Last Year:

PRLY =

VAR

CurrentDate = [Date Reference]

RETURN

CALCULATE(

SUM(Dashboards_Merch[Product Revenue]),

Dashboards_Merch[Order_Year] = YEAR(CurrentDate)-1

)

YoY PR: 

YoY PR = DIVIDE([PRTY], [PRLY], 0)-1

[Greg_Deckler]

Well, if you tried that, you would probably get an error about variant data types. You would need to replace with a number like 0 or BLANK:

Measure =

  VAR __Variable = <code>

RETURN

  IF(__Variable = -1,BLANK,__Variable)

[ehanson2]

@Greg_Deckler i'm having trouble getting this to work. what am i missing here? The RETURN part throws an "unexpected expression" 

Measure =

  [YoY PR] = (DIVIDE([PRTY], [PRLY], 0)-1)

RETURN

  IF([YoY PR] = -1,BLANK,[YoY PR])

[Anonymous · Accepted solution]

HI @ehanson2,

You can add if statement with the condition to compare with static value and return the result.

Measure =
IF([YoY PR] = -1,"New",[YoY PR]&"")

Notice: measure not support to return different type of values at the same time, if you want to use both text and numeric value, please change numeric to text first.

Regards,

Xiaoxin Sheng