Search a text from a string within a rows of a column

[Anonymous]

Hi,

I want to search a text (COWN /CUST Owned /Customer owned) in a description  as shown below:

And here is my criteria

1- If Description has a text COWN/CUST owned or Customer owned and Type is not ZNB1 then return me value of 1 else 2.

Here is what i want.

So far i tried this dax measure but this is not working

Highlight Material Description = maxx(zcvrMaterial,

/*Material Decsription - COWN, CUST OWN, CUSTOMER OWN, CUSTOWN, CUSTOMER OWNED, CUSTOMER OWNE*/

IFERROR( search("CUSTOMER OWNED",[Material description ]),1))\

Any help is greatly appreacited !

Thanks,

Tejaswi

[TomMartens · Accepted solution]

Hey, 

I just realized that the measure in my previous post just solves the text search part of your question, but is neglecting the second condition regarding the value of the product type.

This measure considers all of the requirements:

Measure 2 = 
var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}})
return
//COUNTROWS(searchtable)
IF(HASONEVALUE('Sheet1'[Material description ])
, 
SUMX(
    //VALUES(Sheet1[Material description ])
    'Sheet1'
    , var _d = ROW("d" , 'Sheet1'[Material description ])
    return
    IF(
        'Sheet1'[Material type] = "ZNB1"
        , 2
        , var isFound = 
        MAXX(
            ADDCOLUMNS(
                GENERATEALL(
                    _d
                    , searchtable
                )
                , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK())
            )
        , [contains]
        )
        return
        IF(NOT(ISBLANK(isFound)), 1 , 2) 
    )
)
, BLANK()
)

I added a check (using HASONEVALUE) to avoid summing the values returned by the conditions as I wasn't able to interpret the total line.

And a fraction of the table visual:

Hopefully this is what you are looking for.

Regards,

Tom

[TomMartens]

Hey,

please provide pbix and/or xlsx that contains sample data but still represents your data model (the tables necessary or to be considered to solve your problem), upload the file(s) to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Hi @TomMartens 

Thanks for your reply!

Please find dropbox link to the sample data file.

https://www.dropbox.com/s/st05yn0wq61kr98/sample%20Data.pbix?dl=0

I am looking for a dax measure. It looks like the measure I have is completely wrong.

In this sample data : 

Since  the following products don't fit the criteria and therefore the material description should be highlighted.

- 1433664

-1011046

-1372704

-1233491

Your help in resolving this is greatly appreaciated!

Thanks,

Tejaswi

[Anonymous]

@TomMartens 

Hi Tom,

Did you get a chance to work on this ?

Appreaciate your help!

Thanks,

Tejaswi

[TomMartens]

Hey @Anonymous ,

this measure

Measure 2 = 
var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}})
return
//COUNTROWS(searchtable)
SUMX(
    VALUES(Sheet1[Material description ])
    , var _d = ROW("d" , 'Sheet1'[Material description ])
    return
    MAXX(
        ADDCOLUMNS(
            GENERATEALL(
                _d
                , searchtable
            )
            , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK())
        )
    , [contains]
    )
)

allows to create this table visual:

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi @TomMartens ,

Thanks for providing me the solutions.

However, is it possible to show all the Product number and not just 2 which we get with this solution?

Really appreaciate it!

Thanks,

Tejaswi

[TomMartens · Accepted solution]

Hey, 

I just realized that the measure in my previous post just solves the text search part of your question, but is neglecting the second condition regarding the value of the product type.

This measure considers all of the requirements:

Measure 2 = 
var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}})
return
//COUNTROWS(searchtable)
IF(HASONEVALUE('Sheet1'[Material description ])
, 
SUMX(
    //VALUES(Sheet1[Material description ])
    'Sheet1'
    , var _d = ROW("d" , 'Sheet1'[Material description ])
    return
    IF(
        'Sheet1'[Material type] = "ZNB1"
        , 2
        , var isFound = 
        MAXX(
            ADDCOLUMNS(
                GENERATEALL(
                    _d
                    , searchtable
                )
                , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK())
            )
        , [contains]
        )
        return
        IF(NOT(ISBLANK(isFound)), 1 , 2) 
    )
)
, BLANK()
)

I added a check (using HASONEVALUE) to avoid summing the values returned by the conditions as I wasn't able to interpret the total line.

And a fraction of the table visual:

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi @TomMartens ,

Thanks a Lot.  This code works as expected!

Appreacite all your help!

Thanks,

Tejaswi

[Anonymous]

Hi @TomMartens,

I just noticed, that this code works only if the text is at the beginning.

if text is anywhere in the string or at the end it doesn't work.

Can you please take a look?

Thanks

Tejaswi

[TomMartens · Accepted solution]

IF(FIND([searchtext] , [d] ,1 , BLANK())  >= 1 , 1, BLANK())

Hey @Anonymous 

it seemed that I overlooked a tiny thing between testing and copying the DAX to the post.

I highlighted the change in the DAX statement below:

Measure 2 = 
var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}})
return
//COUNTROWS(searchtable)
IF(HASONEVALUE('Sheet1'[Material description ])
, 
SUMX(
    //VALUES(Sheet1[Material description ])
    'Sheet1'
    , var _d = ROW("d" , 'Sheet1'[Material description ])
    return
    IF(
        'Sheet1'[Material type] = "ZNB1"
        , 2
        , var isFound = 
        MAXX(
            ADDCOLUMNS(
                GENERATEALL(
                    _d
                    , searchtable
                )
                , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK())  >= 1 , 1, BLANK())
            )
        , [contains]
        )
        return
        IF(NOT(ISBLANK(isFound)), 1 , 2) 
    )
)
, BLANK()
)

As FIND(...) returns the position of the searchtext inside the text I changed

IF(FIND([searchtext] , [d] ,1 , BLANK())  = 1 , 1, BLANK())

to this

IF(FIND([searchtext] , [d] ,1 , BLANK())  >= 1 , 1, BLANK())

Regards,

Tom

[Anonymous]

Thanks @TomMartens