Issue with counting rows based on a value in another table

[msimmonsmcse]

I am trying to figure out a measure that will count the number of tickets assigned to a technician. The tricky part is that data coming from my system allows multiple resources to be assigned to a ticket and stores them in its database as a string value with each MemberId seperated by a comma (see sample data below). I want to know exactly how many tickets are assigned to each member based on the Resource_list value containing a match with the MemberID in the Member table. To make matters trickier, the two tables have an inactive relationship between the Ticket_OwnerID field and the MemberID field.

Tickets Table

TicketID	Resource_list	Ticket_OwnerID
1	JoeS	JoeS
2	JoeS,JaneD	JoeS
3	JoeS,MaxF	MaxF
4	MaxF	JaneD
5	JaneD,MaxF	JoeS
6	JoeS,JaneD,MaxF	MaxF

Member Table

MemberID	MemberName
JoeS	Joe Salami
JaneD	Jane Donut
MaxF	Max Freeze

Expected Result of Count of Tickets Assigned measure:

Joe	4
Jane	3
Max	4

[Anonymous · Accepted solution]

Hi, @msimmonsmcse 

You can create a new measure and try the following DAX:

MEASURE = 
VAR _currentID =
    SELECTEDVALUE ( 'Member Table'[MemberID] )
VAR _Vtable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS ( 'Member Table', "_MID", 'Member Table'[MemberID] ),
            SELECTCOLUMNS ( 'Tickets Table', "_R_L", 'Tickets Table'[Resource_list] )
        ),
        FIND ( [_MID], [_R_L],, BLANK () ) <> BLANK ()
    )
RETURN
    COUNTX ( FILTER ( _Vtable, _currentID = [_MID] ), [_MID] )

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.