Forum Discussion

jaideepnema's avatar
jaideepnema
Solution Sage
6 years ago
Solved

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 ABBBCCD...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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!

Geradav's avatar
Geradav
Responsive Resident
6 years ago

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

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      jaideepnema
      Solution Sage
      6 years ago

      parry2k ya that is what i am looking for 😊

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        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!

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    6 years ago

    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 .