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.
MESSAGE | DESIRED RESULT | FORMULA 1 RESULT | FORMULA 2 RESULT |
Certification #1 | 1 | #1 | 1 |
Certification #2 | 2 | #2 | 2 |
Certification #3 | 3 | #3 | 3 |
2017 Live Webinar #1a | 1a | #1a | 1a |
2017 Live Webinar #1b | 1b | #1b | 1b |
2017 Fall Party | 201 | ||
Committee Nominations | Com |
I’m working with email message data. Sometimes, there are multiple messages with the same title, distinguished by order number (#1, #2A, etc)
If the message contains a “#,” I’m looking to extract the 3 characters after the “#” sign in a the text string of the email message title. If the message does not contain the “#” character, I want to return a blank.
If I use Formula 1, it returns the blanks, but it also returns the “#” sign, which I don’t want:
Formula 1 = IFERROR(MID(Emails[Message], FIND("#",Emails[Message],1,BLANK()),3),"")
Formula 2 strips off the “#”, but it also returns the first 3 characters of the Messages that don’t contain “#,” instead of the blanks I want:
Formula 2 = IFERROR(MID(Emails[Message], FIND("#",Emails[Message],1,BLANK())+1,3),"")
I can't seem to find the proper order for the IFERROR statement and its associated blank.
Solved! Go to Solution.
Hi, try with the follow dax:
Column = VAR FINDIT = FIND ( "#", Table4[MESSAGE], 1; 0 ) RETURN IF ( FINDIT > 0, MID ( Table4[MESSAGE], FINDIT+1, 3 ) )
That gave me the same results as Formula 1 -- the "#" is in front of the Message order numbers.
Apologies,
I missed the +1.
That worked!
Thanks.
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |