Measure 'Contains' multiple.

[JimmyBos]

Hello all, Have another question regarding one of my formula's. Am sure the solution is easy!

How to write the following Measure easier.

Bouten&Moeren =

CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Tapbout")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Moer, zeskant")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Moer, zelfborgend")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Carosseriering")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Stafstaal")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Sluitring")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Onderlegring")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Draadstang")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Lasstiftbout")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Assemblage")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Dummy")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Slotbout")|| CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]),"Stelschroef")

[v-nmadadi-msft · Accepted solution]

Hi @JimmyBos,

The issue with SUMX(SearchTerms) was that SearchTerms was defined as a list instead of a table, and DAX functions like SUMX require a table as the first argument. To fix this, SELECTCOLUMNS was used to convert SearchTerms into a one-column table. Additionally, the expression CONTAINSSTRING(Description, SearchTerms) was incorrect because SearchTerms was not a column reference. The fix involved changing SearchTerms to [Term], which correctly references the column from the newly created table.

Here is the code that worked for me with the sample data I have taken

BoutenMoerenTest = 
VAR SearchTerms = 
    ADDCOLUMNS( 
        DATATABLE("Term", STRING, 
            { 
                {"Tapbout"}, {"Moer, zeskant"}, {"Moer, zelfborgend"}, {"Carosseriering"},
                {"Stafstaal"}, {"Sluitring"}, {"Onderlegring"}, {"Draadstang"},
                {"Lasstiftbout"}, {"Assemblage"}, {"Dummy"}, {"Slotbout"}, {"Stelschroef"} 
            }
        ), 
        "Match", IF(CONTAINSSTRING(SELECTEDVALUE('Table'[description]), [Term]), 1, 0)
    )
    
VAR escription = SELECTEDVALUE('Table'[description])

RETURN
IF(
    NOT ISBLANK(escription) && 
    SUMX(SearchTerms, [Match]) > 0, 
    TRUE(), 
    FALSE()
)

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[JimmyBos]

Hello @v-nmadadi-msft , Great, that was the issue. Thank for helping me out!

[TomMartens]

Hey @JimmyBos ,

The measure can be simplified by creating a table that contains all the "searchphrase", in the example below I only use four because I'm lazy.
Then I use the COUNTX iterator to iterate across the table of searchphrases. If the selected value contains one or more of the searchphrases countx returns a value greater than 0.

Bouten&Moeren =
var _selectecdValue = SELECTEDVALUE(POStuklijst[description])
var searchphrasetable = DATATABLE("searchphrase", STRING ,{
{"Tapbout"},{"Moer"}, {"zeskant"},{"Moer"}, {"zelfborgend"}
})
var result =
if(countx(
searchphrasetable,
var searchphrase = [searchprhase]
return
if(containsstring(_selectecdValue , searchphrase ), 1, blank())
) > 0,  "containssearchstring", "doesnotcontainsearchstring")

 Hopefully, this provides what you need to tackle your challenge.

Regards,

Tom

[pankajnamekar25]

Hello Jimmy,

You can try below measure

Bouten&Moeren =
VAR SearchTerms =
{"Tapbout", "Moer, zeskant", "Moer, zelfborgend", "Carosseriering",
"Stafstaal", "Sluitring", "Onderlegring", "Draadstang",
"Lasstiftbout", "Assemblage", "Dummy", "Slotbout", "Stelschroef"}
VAR Description = SELECTEDVALUE(POStuklijst[description])
RETURN
IF(
NOT ISBLANK(Description) &&
SUMX(SearchTerms, CONTAINSSTRING(Description, [Value])) > 0,
TRUE(),
FALSE()
)

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[JimmyBos]

Hello @pankajnamekar25 , Thanks you for the reply. This is what i am looking for but there is an error left.

The syntaxis for Description is incorrect. (DAX(VAR SearchTerms={"Tapbout"............} VAR Description = SELECTEDVALUE(POStuklijst[Description])RETURNIF(NOT ISBLANK(Description)&&SUMX(SearchTerms, CONTAINSSTRING(Description, [Value]...

Any idea how to fix this one?

[pankajnamekar25]

Bouten&Moeren =
VAR SearchTerms =
SELECTCOLUMNS(
{"Tapbout", "Moer, zeskant", "Moer, zelfborgend", "Carosseriering",
"Stafstaal", "Sluitring", "Onderlegring", "Draadstang",
"Lasstiftbout", "Assemblage", "Dummy", "Slotbout", "Stelschroef"},
"Term"
)
VAR Description = SELECTEDVALUE(POStuklijst[description])
RETURN
IF(
NOT ISBLANK(Description) &&
SUMX(SearchTerms, CONTAINSSTRING(Description, [Term])) > 0,
TRUE(),
FALSE()
)

try this

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[JimmyBos]

Hello @pankajnamekar25 , I tried your solution, but no unfortunately no succes. The error remains the same. I think there might be an issue with 'VAR Description = SELECTEDVALUE(POStuklijst[description])'.

[v-nmadadi-msft]

Hi @JimmyBos,
Thanks for reaching out to the Microsoft fabric community forum.

No, the syntax is not correct because SUMX requires a numeric expression, but CONTAINSSTRING returns a Boolean (TRUE/FALSE).
The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

Please refine the following and verify that it functions correctly.
SUMX(SearchTerms, IF(CONTAINSSTRING(Description, SearchTerms[Term]), 1, 0)

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[JimmyBos]

Hello @v-nmadadi-msft , Thanks for helping me out here. Most likely i misunderstand the problem, but when i tried to change the formula @pankajnamekar25 send, it still returns the same error. The formula i tried:

Bouten&Moeren test =

VAR SearchTerms =

{"Tapbout", "Moer, zeskant", "Moer, zelfborgend", "Carosseriering",

"Stafstaal", "Sluitring", "Onderlegring", "Draadstang",

"Lasstiftbout", "Assemblage", "Dummy", "Slotbout", "Stelschroef"}

VAR Description = SELECTEDVALUE(POStuklijst[description])

RETURN

IF(

NOT ISBLANK(Description) &&

SUMX(SearchTerms, IF(CONTAINSSTRING(Description, SearchTerms), 1, 0)) > 0,

TRUE(),

FALSE()

)

[v-nmadadi-msft · Accepted solution]

Hi @JimmyBos,

The issue with SUMX(SearchTerms) was that SearchTerms was defined as a list instead of a table, and DAX functions like SUMX require a table as the first argument. To fix this, SELECTCOLUMNS was used to convert SearchTerms into a one-column table. Additionally, the expression CONTAINSSTRING(Description, SearchTerms) was incorrect because SearchTerms was not a column reference. The fix involved changing SearchTerms to [Term], which correctly references the column from the newly created table.

Here is the code that worked for me with the sample data I have taken

BoutenMoerenTest = 
VAR SearchTerms = 
    ADDCOLUMNS( 
        DATATABLE("Term", STRING, 
            { 
                {"Tapbout"}, {"Moer, zeskant"}, {"Moer, zelfborgend"}, {"Carosseriering"},
                {"Stafstaal"}, {"Sluitring"}, {"Onderlegring"}, {"Draadstang"},
                {"Lasstiftbout"}, {"Assemblage"}, {"Dummy"}, {"Slotbout"}, {"Stelschroef"} 
            }
        ), 
        "Match", IF(CONTAINSSTRING(SELECTEDVALUE('Table'[description]), [Term]), 1, 0)
    )
    
VAR escription = SELECTEDVALUE('Table'[description])

RETURN
IF(
    NOT ISBLANK(escription) && 
    SUMX(SearchTerms, [Match]) > 0, 
    TRUE(), 
    FALSE()
)

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[JimmyBos]

Hello @v-nmadadi-msft , Thanks once more for you assistance. I have tried the formula you used, for this give the same error. Any idea how that is possible?

The formula:

BoutenMoerenTest =

VAR SearchTerms =

    ADDCOLUMNS(

        DATATABLE("Term", STRING,

            {

                {"Tapbout"}, {"Moer, zeskant"}, {"Moer, zelfborgend"}, {"Carosseriering"},

                {"Stafstaal"}, {"Sluitring"}, {"Onderlegring"}, {"Draadstang"},

                {"Lasstiftbout"}, {"Assemblage"}, {"Dummy"}, {"Slotbout"}, {"Stelschroef"}

            }

        ),

        "Match", IF(CONTAINSSTRING(SELECTEDVALUE(POStuklijst[description]), [Term]), 1, 0)

    )

VAR Description = SELECTEDVALUE(POStuklijst[description])

RETURN

IF(

    NOT ISBLANK(Description) &&

    SUMX(SearchTerms, [Match]) > 0,

    TRUE(),

    FALSE()

)

The error:

The syntax for Description is incorrect. (DAX(VAR SearchTerms = ADDCOLLUMNS(DATATABLE("term", STRING, {{"Tapbout"}.......

[v-nmadadi-msft · Accepted solution]

Hi @JimmyBos,

Please use a different variable name instead of "Description" in your DAX code, as it may conflict with a reserved keyword or cause syntax issues. I encountered the same error previously, which is why I changed the variable name in the DAX code I shared with you.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards