CONTAINSTRING not working in FILTER statement?

[JedShields]

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?

[v-yilong-msft · Accepted solution]

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 · Accepted solution]

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]

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

[JedShields]

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

[v-yilong-msft · Accepted solution]

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.

[vivek31]

HI @JedShields ,

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

[BeaBF]

@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