SUMIFS function for unrelated tables using CONTAINS

[grdi]

I have 2 tables that are unrelated.  Essentially an employee master table, a table that essentially logs activities and assigns Yes No values.  If multiple employees perform the activity, for whatever reason, they are input into the field seperated by comma.  And no, there is no detail table that breaks them out.  

So what I want to do is to create measures that will look at the master table, and do either a SumIfs or CountIfs style function to see if their ID appears in the column, and sum the columns.  One measure for each of Yes and No in the example data.  

So an example of the master data:

empID	empName
A1B2	John
C3D4	Jack
E5F6	Jane
G7H8	Janet

Then how it shows in the activity table:

Activity	Yes	No
A1B2	1	0
C3D4, A1B2	0	1
C3D4, A1B2, G7H8	1	0
E5F6	0	1

And what I'd like to have as the result.

empID	empName	YES	NO
A1B2	John	2	1
C3D4	Jack	1	1
E5F6	Jane	0	1
G7H8	Janet	1	0

In Excel, it would be something like =SUMIFS(columnYES,columnActivity,"*"&field_empID&"*")

I'm trying to create a measure on the Employee Master table in the PowerBI data that does something like that.  But because of the lack of relationship it's not working correctly.  

Thanks for the help.

[danextian · Accepted solution]

Hi @grdi 

Try this:

Sum of Yes = 
CALCULATE (
    SUM ( activityTable[Yes] ),
    KEEPFILTERS (
        CONTAINSSTRING ( activityTable[Activity], SELECTEDVALUE ( empTable[empID] ) )
    )
)

Change the column name for the sum of no.

[grdi]

Thanks for the reply @danextian .  I'm using your formula but not getting the correct results.  For some reason I'm only getting a count of 1 for one employee.  And I know there should be about 15 employees with a combined total of 60+ for this calculation.  Also odd that the name shows one but the total is 208.  I think those are the ones with no match?  Which is possible as well as this is supposed to only be for a specific subset of employees.  Here is the code I'm using and the result I'm getting.

EDITING HERE:  I beleive an existing relationship with another column is what was causing my formula not to work as the solution.  I uploaded a duplicate table with the additional columns that formed the relationship removed, and it worked as intended.  So solution is good and will mark it as such.  Wanted to include the edit for future reference for others searching.  

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.