Validate a String Pattern in a Column using DAX

[jaideepnema]

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 ?

[Geradav]

@jaideepnema 

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

[jaideepnema]

@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 .

[parry2k]

@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 

[jaideepnema]

@parry2k ya that is what i am looking for 😊

[parry2k · Accepted 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!

[amitchandak]

Try a new column like

if(search("BCD",[String Pattern],,0)>0,"Yes","No")

[parry2k]

@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.

[jaideepnema]

@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

[amitchandak]

@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")