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.
I have three columns that I need to base a new calculated column on where I need to return a value based on two IF statments, one that uses a date and one that finds a particular text within a string.
My columns are Date of Sales, Notes.1.1 (this is a date field corresponding to when a customer has called in to customer service), and Notes.2 (this is a text string of user inputted notes)
What I'm trying to do is the following calculated column but am not geting the value passed.
Customer Cancel =
IF(fSales[Notes.1.1]=fSales[Date of Sale]
&& IF(ISBLANK(SEARCH("Cancelled",fSales[Notes.2])),1,BLANK()),
1,BLANK())
I'm sure this isn't right. Any help would be appreciated.
Solved! Go to Solution.
This should give you 1 if they ordered and then called to cancel the same day (that's basically how I read your question)
Customer Cancel Column = IF ( fSales[Date of Sale] = fSales[Notes.1.1], IF ( ISBLANK ( SEARCH ( "Cancelled", fSales[Notes.2], 1, BLANK () ) ), BLANK (), 1 ), BLANK () )
This should give you 1 if they ordered and then called to cancel the same day (that's basically how I read your question)
Customer Cancel Column = IF ( fSales[Date of Sale] = fSales[Notes.1.1], IF ( ISBLANK ( SEARCH ( "Cancelled", fSales[Notes.2], 1, BLANK () ) ), BLANK (), 1 ), BLANK () )
Thanks Sean!
That totally worked. Now what if I also wanted to include variations on the spelling of "Cancelled"? Such as Cancel, cncl, cncld.
Is that even possible?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |