Sum the number of times a string appears in data

[Anonymous]

Good afternoon everyone,

I am trying to figure out a way to accurately show how many times an installer had an installation. The problem that i am running into though is that the column of information that records who the installer was can sometimes contain multiple installers (if there were multiple installers present). I have 2 tables, one with the orders and one with the list of possible installers. So for example my data may look like:

Base Data

Order	Installer
1234	Joe A
5678	Jim B,Joe A
9012	Jim B,Joe A,Frank R

Technicians

Installer

Joe A

Jim B

Frank R

I've tried using a measure that contains the following:

Installer Instances = 
SUMX(
    BaseData, 
    IF(
        CONTAINSSTRING(BaseData[Installer], SELECTEDVALUE(Technicians[Installer])), 1,0
    )
)

 Unfortunately that doesn't seem to find names if there are multiple per cell. Any ideas, i'm kinda stumped?

Thanks in advance!

[Zubair_Muhammad]

@Anonymous 

What is your expected result in above case?

[Anonymous]

My hope is that if i reduce the number of displayed orders, via a slicer based on date, that i can show the correct totals for the number of times installers have been deployed.

So, in the data set that i provided my numbers would be

Joe A. - 3

Jim B. - 2

Frank R. - 1

I would then display that result via a bar graph.

[Zubair_Muhammad]

@Anonymous 

I tested your formula and it gives the stated results.

Am i missing something?

[Anonymous · Accepted solution]

I think i figured out a solution myself.

I took a big step back and instead just split the data in that column to create multiple rows. Once i changed the relationship data it seemed to be happy.

Not sure why that formula wouldn't work for me, but thank you for the help all the same!