Forum Discussion
Validate a String Pattern in a Column using DAX
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 ?
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!
9 Replies
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.
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")
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_inTheStringLet us know if that works for you
Regards
David
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
parry2k ya that is what i am looking for 😊
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 .
