Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
I found the solutions thanks anyway
Thank you so much this worked.
Hi,
Thank you for the reply I have tried your solution but facing the below issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |