CONTAINS_TEXT DAX for calculated column

[neelofarshama]

Hi all,

I have a column named "offer" which has data as shown below

 

I have to create a new column using DAX with the below case statement

case
when CONTAINS_TEXT(Offer,'08W001.00') then '8 Weeks | $1.00'
when CONTAINS_TEXT(Offer,'04W004.00') then '4 Weeks | $4.00'
when CONTAINS_TEXT(Offer,'04W001.00') then '4 Weeks | $1.00'
when CONTAINS_TEXT(Offer,'04W000.99') then '4 Weeks | $0.99'
when CONTAINS_TEXT(Offer,'13W001.00') then '13 Weeks | $1.00'
when CONTAINS_TEXT(Offer,'13W000.00') then '13 Weeks | $0.00'
when CONTAINS_TEXT(Offer,'08W007.92') then '8 Weeks | $7.92'
when CONTAINS_TEXT(Offer,'26W006.50') then '26 Weeks | $6.50'
when CONTAINS_TEXT(Offer,'52W000.00') then '52 Weeks | $0.00'
when CONTAINS_TEXT(Offer,'03Y000.00') then '3 Years | $0.00'
when CONTAINS_TEXT(Offer,'01W000.00') then '1 Week | $0.00'
when CONTAINS_TEXT(Offer,'26W000.00') then '26 Weeks | $0.00'
when CONTAINS_TEXT(Offer,'52W098.00') then '52Weeks | $98.00'
else Offer end.

Any help is highly appreciated.

[v-alq-msft · Accepted solution]

Hi, @neelofarshama 

You may try the following calculated column. 

Result = 
SWITCH(
    TRUE(),
    CONTAINSSTRINGEXACT('Table'[offer],"08W001.00"),"8 Weeks | $1.00",
    CONTAINSSTRINGEXACT('Table'[offer],"04W004.00"),"4 Weeks | $4.00",
    CONTAINSSTRINGEXACT('Table'[offer],"04W001.00"),"4 Weeks | $1.00",
    CONTAINSSTRINGEXACT('Table'[offer],"04W000.99"),"4 Weeks | $0.99",
    CONTAINSSTRINGEXACT('Table'[offer],"13W001.00"),"13 Weeks | $1.00",
    CONTAINSSTRINGEXACT('Table'[offer],"13W000.00"),"13 Weeks | $0.00",
    CONTAINSSTRINGEXACT('Table'[offer],"08W007.92"),"8 Weeks | $7.92",
    CONTAINSSTRINGEXACT('Table'[offer],"26W006.50"),"26 Weeks | $6.50",
    CONTAINSSTRINGEXACT('Table'[offer],"52W000.00"),"52 Weeks | $0.00",
    CONTAINSSTRINGEXACT('Table'[offer],"03Y000.00"),"3 Years | $0.00",
    CONTAINSSTRINGEXACT('Table'[offer],"01W000.00"),"1 Week | $0.00",
    CONTAINSSTRINGEXACT('Table'[offer],"26W000.00"),"26 Weeks | $0.00",
    CONTAINSSTRINGEXACT('Table'[offer],"52W098.00"),"52Weeks | $98.00",
    "end"
)

From your smaple data, all records contains '08W001.00'. So the result is as below.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[neelofarshama]

Hi Allan,

Thanks for the help, this is working now, but all other rows in the column are returning blank values which I dont want.

I want the other values to display as it is in the new calculated column.

Below is the screenshot for reference.

[neelofarshama]

I found the solutions thanks anyway 

[neelofarshama]

Thank you so much this worked.

[Greg_Deckler]

SWITCH(TRUE(),
SEARCH("08W001.00",[Offer]),"8 Weeks | $1.00",
SEARCH("08W004.00",[Offer]),"4 Weeks | $4.00",
...
)

[neelofarshama]

Hi,

Thank you for the reply I have tried your solution but facing the below issue.