How to search for a specific text within column in power bi using DAX

[Alice08]

I want to search for a text called "Sprint 1" within column "Iteration Path", but this column contains value as "Sprint 11" ,"Sprint 12" etc...

How can i limit the search and find the exact match?

[Sakshi_Kank_08]

ExactMatch = IF(
[Iteration Path] = "Sprint 1",
"True",
"False"
)

ExactMatch

Iteration Path
Sprint 1	True
Sprint 11	False
Sprint 12	False
Sprint 1	True
Sprint 10	False
Sprint 2	False

[Thejeswar]

Hi @Alice08 ,
Using CONTAINSSTRING() and CONTAINSSTRINGEXACT() will give the same output in this case. Find below the screenshot

Instead you have to use either of these functions with a customized condition like the one given below

IF(AND(CONTAINSSTRINGEXACT(SELECTEDVALUE('Table'[SprintName]), "Sprint 1"), LEN(SELECTEDVALUE('Table'[SprintName])) == LEN("Sprint 1")), TRUE, FALSE)

Regards,

[Alice08]

@Thejeswar  I have tried this but its giving same output , i want to create a column which will contain exact match for Sprint 1.

Now in this case Sprint 10 as well it is detecting as Sprint 1.

[Thejeswar]

Hi @Alice08 ,

You should create a measure using the DAX formula I shared.

If you want to create a column that gives the same output, you need to slightly modify the DAX formula like given below

IF(AND(CONTAINSSTRINGEXACT('Table'[SprintName], "Sprint 1"), LEN('Table'[SprintName]) == LEN("Sprint 1")), TRUE, FALSE)

Regards,

[Alice08]

This is giving me False output for all.

[Samarth_18]

Hi @Alice08 ,

You can use the CONTAINSSTRINGEXACT function. Refer the below article.

https://learn.microsoft.com/en-us/dax/containsstringexact-function-dax

[Alice08]

@Samarth_18  This is again giving me same output.

[Samarth_18]

Hi @Alice08 , Please try this:-

Column = IF(EXACT('Table'[Iteration Path],"Sprint 1"),TRUE(),FALSE())

[Alice08]

This as well not working.

[Samarth_18]

It seems something is missing. I have replicated the same scenario and it works fine as a column

and if you are trying to create a measure then use this code:-

Measure 2 = IF(EXACT(MAX('Table'[Iteration Path]),"Sprint 1"),TRUE(),FALSE())

If it still has not helped. I request you to please share your sample file.

[Alice08]

i think its not working for me because Iteration Path column does not contain the exact value as "Sprint 1" it has values like "Project\Q1\Sprint 1"

[Samarth_18 · Accepted solution]

Thanks for sharing the exact values from the data. In this case you could try following code:-

Column_ = 
VAR _search =
    SEARCH ( "Sprint 1", [Iteration Path] )
VAR _search_sprint =
    MID ( [Iteration Path], _search, LEN ( [Iteration Path] ) )
RETURN
    IF ( _search_sprint = "Sprint 1", TRUE (), FALSE () )