Forum Discussion

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
5 years ago
Solved

String block search

Hi and thanks for reading this. Have tried to find an answer for this but no luck.   Is it possible to do a textsearch that search in blocks of every 3 letter in a string.   Lets say i am lookin...
Need Help
  • TomMartens's avatar
    TomMartens
    5 years ago

    Hey Wresen ,

     

    first I created a table that contains the "predefined blocks", I called this table "searchstrings" and the column "searchstring", the table looks like this:

    The table is NOT related to the item-table.

    Then I created this measure:

    Measure = 
var __SearchstringsToFind =  VALUES( 'Searchstrings'[searchstring] )
var lengthOfBlock = 3
return
COUNTX(
    Sheet1
    , var afunnystring = 'Sheet1'[String]
    var lengthofstring = LEN( afunnystring )
    var noofoccurrences = 
        IF( MOD( lengthofstring , lengthOfBlock ) = 0
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) )
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) ) + 1
        )
    var __blocks = 
        ADDCOLUMNS(
            GENERATESERIES( 1 , noofoccurrences )
            , "block" , MID( afunnystring , ''[Value] * lengthOfBlock - 2 , lengthOfBlock )
        )
    return
    COUNTROWS(
        FILTER(
            __blocks
            , [block] in __SearchstringsToFind
        )
    )
)

    It seems the measure is returning the expected result:

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
5 years ago

Hi TomMartens 

I have added some more data , still simple but more:

If we look for abc , the correct ones are Green , Yellow, Black and Purple.

My thoug it that the user should be able trough a filter choose combinations and get the value of the items summed .

Ex he chooses abc and cee . The sum would be total 5 items with these combo (Green, Yellow, Black, Purple and also now Red (cee combo)

 

I hope you understand what i try to explain .

/Thanks

edit , added link:

https://drive.google.com/file/d/1VTKJf6ZXxG99vrZBEa6lbzGedRzuyVsz/view?usp=sharing

 

 

ItemString
Greenabcbabcdd
Bluecdeababcd
Yellowbbcabcabd
Brownbabcabcda
Blackaabcbaabc
Redababcacde
Purpleacdacaabc
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
5 years ago

Hey Wresen ,

 

first I created a table that contains the "predefined blocks", I called this table "searchstrings" and the column "searchstring", the table looks like this:

The table is NOT related to the item-table.

Then I created this measure:

Measure = 
var __SearchstringsToFind =  VALUES( 'Searchstrings'[searchstring] )
var lengthOfBlock = 3
return
COUNTX(
    Sheet1
    , var afunnystring = 'Sheet1'[String]
    var lengthofstring = LEN( afunnystring )
    var noofoccurrences = 
        IF( MOD( lengthofstring , lengthOfBlock ) = 0
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) )
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) ) + 1
        )
    var __blocks = 
        ADDCOLUMNS(
            GENERATESERIES( 1 , noofoccurrences )
            , "block" , MID( afunnystring , ''[Value] * lengthOfBlock - 2 , lengthOfBlock )
        )
    return
    COUNTROWS(
        FILTER(
            __blocks
            , [block] in __SearchstringsToFind
        )
    )
)

It seems the measure is returning the expected result:

Hopefully, this provides what you are looking for.

 

Regards,

Tom