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

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