cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver II
Resolver II

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 

IDString Pattern
1ABCDE
2ABBCCDDE
3ABDE
4ACDE
5ABBBCCDE
6AE
7ABBCCDDDDE

 

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

IDString PatternCheck
1ABCDEYes
2ABBCCDDEYes
3ABDENo
4ACDENo
5ABBBCCDEYes
6AENo
7ABBCCDDDDEyes

 

Please let me know how this can be achieved using DAX in Power BI Desktop ?

1 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!






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.





View solution in original post

9 REPLIES 9
Resolver II
Resolver II

@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

 

Annotation 2020-04-27 184333.jpg

 

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 

 

image.png






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 ya that is what i am looking for 😊

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






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.





View solution in original post

Super User IV
Super User IV

Try a new column like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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






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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors