Forum Discussion

Wresen's avatar
Wresen
Post 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

MFelix's avatar
MFelix
Super User
5 years ago

Hi Wresen ,

 

Believe this can be done using an adjustment of a code used before but the question is on the split by the values you are searching.

 

The link is the following

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Find-Words-on-sentence-from-another-selected-sentence/m-p/1602658#M658

 

In your case I have made the following measure:

 

Find Word Formula = 
// Character that split phrase into words
VAR SplitByCharacter = "abc" 

// Temporary table that splits selected phrase into words
VAR Words_table =
    --FILTER (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS (
                    ALLSELECTED ( 'Table'[Column1] ),
                    "Find_Text", 'Table'[Column1] 
                ),
                VAR TokenCount =
                    PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
                RETURN
                    GENERATESERIES ( 1, TokenCount )
            ),
            "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
        )
RETURN
COUNTROWS(Words_table) - 1

 

Has you can see below waht I'm doing is picking up your text line and replacing the text you are looking by a "|" and then creating a line for each of the |

 

In this case you would get the following table:

This is your text but without the ABC if we count the number of rows and subtract 1 we get howe many ABC were in the text:

 

Please see attach file.

 

 

 

  • Wresen's avatar
    Wresen
    Post Patron
    5 years ago

    Hi MFelix  and thx so much for the code.

     

    I am not sure the code will work since the code "only" looks for abc , not that its in the right place in the string (possition 1-3 or 4 -6 or7-9 and so on, that what i call in 3 blocks)

    Also the file will have over 500k rows of items that has a this long string on each row and my thougt was that the user could filter these 500k items on a few predifined 3 block strings (the 3 block strings is a defining of the object).