topic Re: CONTAINSTRING not working in FILTER statement? in DAX Commands and Tips

CONTAINSTRING not working in FILTER statement?

JedShields — Wed, 22 Jan 2025 15:59:40 GMT

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?

Re: CONTAINSTRING not working in FILTER statement?

BeaBF — Wed, 22 Jan 2025 16:32:52 GMT

JedShields Hi! Try to test the two conditions indipendently:

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

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

which is not working?

BBF

Re: CONTAINSTRING not working in FILTER statement?

vivek31 — Wed, 22 Jan 2025 17:35:58 GMT

HI JedShields ,

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

Re: CONTAINSTRING not working in FILTER statement?

Anonymous — Thu, 23 Jan 2025 07:25:59 GMT

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.

Re: CONTAINSTRING not working in FILTER statement?

JedShields — Fri, 24 Jan 2025 09:33:12 GMT

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

Re: CONTAINSTRING not working in FILTER statement?

rohit1991 — Sun, 27 Jul 2025 09:01:46 GMT

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.

Re: CONTAINSTRING not working in FILTER statement?

JedShields — Mon, 27 Jan 2025 13:12:07 GMT

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