Forum Discussion

JedShields's avatar
JedShields
Regular Visitor
1 year ago
Solved

CONTAINSTRING not working in FILTER statement?

Hello all,


I've got a measure that counts all sites in a table (keyMSSummary) after filtering sites that have a specific Activity Name, and also filter the column Focus Area for anything that contains "eMVP".  The code below doesn't work and I think it's the CONTAINSTRING part.

    COUNTX(
        FILTER(
            'keyMSSummary',
            'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo"
            && CONTAINSSTRING('keyMSSummary'[focus_area], "eMVP")),
            'keyMSSummary'[ms5_SiteAccessed_ACT]
        )

Any suggestions?
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi JedShields ,

    I create a table as you mentioned.

    Then I think you can change your DAX code:

    Measure = 
    COUNTX(
        FILTER(
            'keyMSSummary',
            'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo"
            && SEARCH("eMVP", 'keyMSSummary'[focus_area], 1, 0) > 0
        ),
        'keyMSSummary'[ms5_SiteAccessed_ACT]
    )

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • rohit1991's avatar
    rohit1991
    1 year ago

    Use FIND instead of CONTAINSSTRING for reliability:

     

    CountSites :=
COUNTX(
FILTER(
'keyMSSummary',
'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo"
&& FIND("eMVP", 'keyMSSummary'[focus_area], 1, 0) > 0
),
'keyMSSummary'[ms5_SiteAccessed_ACT]
)


If focus_area might be blank, handle it with COALESCE:

FIND("eMVP", COALESCE('keyMSSummary'[focus_area], ""), 1, 0) > 0

     

    This ensures proper evaluation of the substring check.

6 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Use FIND instead of CONTAINSSTRING for reliability:

     

    CountSites :=
COUNTX(
FILTER(
'keyMSSummary',
'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo"
&& FIND("eMVP", 'keyMSSummary'[focus_area], 1, 0) > 0
),
'keyMSSummary'[ms5_SiteAccessed_ACT]
)


If focus_area might be blank, handle it with COALESCE:

FIND("eMVP", COALESCE('keyMSSummary'[focus_area], ""), 1, 0) > 0

     

    This ensures proper evaluation of the substring check.

    • JedShields's avatar
      JedShields
      Regular Visitor
      1 year ago

      Thanks for the advice, I'll make the changes.

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    JedShields Hi! Try to test the two conditions indipendently:

     

    1.

    COUNTROWS(
    FILTER(
    'keyMSSummary',
    'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo")
    )
    )

     

    2.

    COUNTROWS(
    FILTER(
    'keyMSSummary',
    CONTAINSSTRING('keyMSSummary'[focus_area], "eMVP")
    )
    )

     

     

    which is not working?

     

    BBF

  • vivek31's avatar
    vivek31
    Resolver II
    1 year ago

    HI JedShields ,

    you can use FIND function to place of CONTAINSSTRING 
    FIND("eMVP", 'keyMSSummary'[focus_area], 1, 0) 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi JedShields ,

    I create a table as you mentioned.

    Then I think you can change your DAX code:

    Measure = 
    COUNTX(
        FILTER(
            'keyMSSummary',
            'keyMSSummary'[activity_name] = "4G X Symmetry New POP 4G/5G – Cellnex 127 TowerCo"
            && SEARCH("eMVP", 'keyMSSummary'[focus_area], 1, 0) > 0
        ),
        'keyMSSummary'[ms5_SiteAccessed_ACT]
    )

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • JedShields's avatar
    JedShields
    Regular Visitor
    1 year ago

    Thanks all.

    Apologies for the delay, your solutions all worked, but so did my original CONTAINSSTRING!  It turns out that the text I copied from an excel data source a had a long hyphon in the text, the csv that the PBI report links to has a short hyphon!  Typical XD