Forum Discussion

JimmyBos's avatar
JimmyBos
Helper II
1 year ago
Solved

Measure 'Contains' multiple.

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")
Need Help
Show and Tell
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    1 year ago

    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

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    1 year ago

    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

11 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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's avatar
      JimmyBos
      Helper II
      1 year ago

      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's avatar
        pankajnamekar25
        Super User
        1 year ago

        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.

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    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