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 a requirement to find whether a string given in a row contains a certain pattern. I have data shown below
ID | String Pattern |
1 | ABCDE |
2 | ABBCCDDE |
3 | ABDE |
4 | ACDE |
5 | ABBBCCDE |
6 | AE |
7 | ABBCCDDDDE |
I need to check whether the string pattern BCD (irrespective of whether the characters B,C and D are repeated or not ) is present in each of the values given in the column string pattern between the characters A and E. The occurence of characters BCD should be in the same order only and the order cannot be changed however the characters can be repeated any number of times like BBCCDD,BCCCDD,BBCCDDEEEE etc.
the final output should be like this
ID | String Pattern | Check |
1 | ABCDE | Yes |
2 | ABBCCDDE | Yes |
3 | ABDE | No |
4 | ACDE | No |
5 | ABBBCCDE | Yes |
6 | AE | No |
7 | ABBCCDDDDE | yes |
Please let me know how this can be achieved using DAX in Power BI Desktop ?
Solved! Go to Solution.
@jaideepnema ok, here it is, add it as a column, although I would prefer to add this in Power Query because you can create a function for repetitive tasks.
Anyhow, you can replace string with "Yes" and "No"
Pattern =
VAR __aPos = SEARCH ( "A", pos[String Pattern], , -1 )
VAR __ePos = SEARCH ( "E", pos[String Pattern], , -1 )
VAR __bPos = SEARCH ( "B", pos[String Pattern], , -1 )
VAR __cPos = SEARCH ( "C", pos[String Pattern], , -1 )
VAR __dPos = SEARCH ( "D", pos[String Pattern], , -1 )
RETURN
SWITCH (
TRUE(),
__aPos = -1 || __ePos = -1, "A and E not found", //no value
__bPos = -1 || __cPos = -1 || __dPos = -1, "BCD not found", //no value
( __bPos > __aPos && __bPos < __cPos ) &&
( __cPos > __bPos && __cPos < __dPos ) &&
( __dPos > __cPos && __dPos < __ePos ), "Found", //yes value
"BCD Not between A and E or BCD not in the right order" //no value
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You can try the following
OutputCol =
VAR String = Table2[String Pattern]
VAR myString =
MID ( String, 2, LEN ( String ) - 2 )
VAR B_Position =
FIND ( "B", myString,, 0 )
VAR is_B_inTheString = B_Position > 0
VAR C_Position =
FIND ( "C", myString, B_Position + 1, 0 )
VAR is_C_inTheString = C_Position > 0
VAR D_Position =
FIND ( "D", myString, C_Position + 1, 0 )
VAR is_D_inTheString = D_Position > 0
RETURN
is_B_inTheString
&& is_C_inTheString
&& is_D_inTheString
Let us know if that works for you
Regards
David
@Geradav thanks for your reply .
Although it works with the given set of data. However it doesnt check whether BCD is between A and E only. Also in case i dont have either A or E this is not working .
@jaideepnema I just did the stress test, first checking to make sure it between "A" and "E" and in the right order, B -> C -> D
I added a few more example pattern to check, see if this is what what you are looking for. I just put a string, for now, to see what is the pattern and we can easily convert it to Yes and No, see new row from 7 onwards
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jaideepnema ok, here it is, add it as a column, although I would prefer to add this in Power Query because you can create a function for repetitive tasks.
Anyhow, you can replace string with "Yes" and "No"
Pattern =
VAR __aPos = SEARCH ( "A", pos[String Pattern], , -1 )
VAR __ePos = SEARCH ( "E", pos[String Pattern], , -1 )
VAR __bPos = SEARCH ( "B", pos[String Pattern], , -1 )
VAR __cPos = SEARCH ( "C", pos[String Pattern], , -1 )
VAR __dPos = SEARCH ( "D", pos[String Pattern], , -1 )
RETURN
SWITCH (
TRUE(),
__aPos = -1 || __ePos = -1, "A and E not found", //no value
__bPos = -1 || __cPos = -1 || __dPos = -1, "BCD not found", //no value
( __bPos > __aPos && __bPos < __cPos ) &&
( __cPos > __bPos && __cPos < __dPos ) &&
( __dPos > __cPos && __dPos < __ePos ), "Found", //yes value
"BCD Not between A and E or BCD not in the right order" //no value
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Try a new column like
if(search("BCD",[String Pattern],,0)>0,"Yes","No")
@amitchandak, unfortunately, it will not work, check row 2, 5, 7, your solution will return No where it needs Yes
@jaideepnema really interesting problem, have to think out of the box to solve it. let's what we can do it here. Are you ok if it is done in power query as that might be the right place to work on this? I just want to ask before I go that path.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k and @amitchandak thanks for replying
@parry2k this needs to be done using DAX. I understand the right place to solve this would be using Power Query but i need this to be done using DAX
@parry2k , Thanks for pointing. Looking at 2nd row I had a doubt I missed something. Maybe this can work
if(search("B",[String Pattern],,0)>0 && search("C",[String Pattern],,0)>0 && search("D",[String Pattern],,0)>0,"Yes","No")
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |