Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |