Forum Discussion

msimmonsmcse's avatar
msimmonsmcse
Helper I
2 years ago
Solved

Issue with counting rows based on a value in another table

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

TicketIDResource_listTicket_OwnerID
1JoeSJoeS
2JoeS,JaneDJoeS
3JoeS,MaxFMaxF
4MaxFJaneD
5JaneD,MaxFJoeS
6JoeS,JaneD,MaxFMaxF

 

Member Table

MemberIDMemberName
JoeSJoe Salami
JaneDJane Donut
MaxFMax Freeze

 

Expected Result of Count of Tickets Assigned measure:

Joe4
Jane3
Max4
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.