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(POStukl...
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

v-nmadadi-msft's avatar
v-nmadadi-msft
Community Support
1 year ago

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

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's avatar
    v-nmadadi-msft
    Community Support
    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

    • JimmyBos's avatar
      JimmyBos
      Helper II
      1 year ago

      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's avatar
        v-nmadadi-msft
        Community Support
        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