Correlate two separate date fields in Dax for %Resolved and visuals

[amurph43]

Hi, first time poster! I usually just muddle through and keep trying over and over until I get things cobbled together, but this one is a bit more complicated than ones I've dealt with in the past.  We try to avoid competing values in the same table (2 dates used interchangeably, 2 names used interchangeably) but we couldn't avoid it this time.

PBIX File: Tickets_resolved_test.pbix

Excel File:  PBI_Ticket_resolved_sanitized.xlsx

Current formulas:

• TicketsWorked = DISTINCTCOUNT(Table5[Ticket_Number])
• TicketsResolved = CALCULATE( 
      DISTINCTCOUNT([Ticket_Number]),
      Table5[resolved_by]=SELECTEDVALUE(Table5[member_id]),
      Table5[date_resolved]=SELECTEDVALUE(Table5[date_worked])  )
• %TicketsResolved = [TicketsResolved]/[TicketsWorked]

Goals:
1. Find % of tickets resolved by each selected member(s) of the team during a selected time range

2. Allow you to select different dates on a slicer

3. Show table of member_id, # TicketsWorked, # TicketsResolved, and %Resolved

4. Line chart view of %Resolved per member_id over time

4. When you select multiple of member_id in a slicer, you get totals for TicketsWorked, TicketsResolved, and maybe average of %Resolved.  Also just fine with totals for the first two.

Issues:
1. Sometimes a ticket can be worked on by multiple people but each ticket can only resolved by 1 person.  This results in duplicate rows for the "ticket was worked on" set, but each ticket only has 1 distinct value for resolved_by.  There are other columns not listed in this file (such as their time entry notes, start time and end time for that time they worked the ticket, etc) that are unique and/or distinct per row, so we can't de-duplicate rows.

2. A ticket isn't necessarily closed on the same day it was opened, which means you HAVE to relate date_resolved to date_worked.  If you try it without that it just checks if the ticket was worked on in the time range selected, then checks if the ticket was resolved by a secl

3. This table 2 distinct sets of data revolving around ticket_number: "this ticket_number was worked by this member_id during this datetime_worked" and "this ticket_number was resolved by this resolved_by at this datetime_resolved".

What I've done so far:
1. Finding the number of tickets worked is easy, you just count the number of distinct tickets per member at any given time range.

2. I thought that finding the number of tickets resolved would be just as easy, but I'm having difficulties relating resolved_by to member_id, and date_resolved to date_worked, we don't usually have competing similar values in the same table.
3. In another file of ours, we used X=SelectedValue(Y) to relate things like this, but that method isn't working here, at least for the dates.  I've also run into issues with there being multiple dates (or the full column of dates) referenced in DAX which I've tried to resolve with MAX(DateColumn).

As you can see, no issues with TicketsWorked, but once I try to correlate date_resolved to date_worked in the TicketsResolved DAX then I run into issues.  If I just correlate resolved_by to member_id in TicketsResolved Dax, it gives me a number but also counts tickets that were closed after the selected date as well.

Thanks in advance for any help offered.

[amurph43 · Accepted solution]

Thanks to everyone that replied.  This was actually accomplished by creating a lookup table between the two types, creating an inactive relationship between the lookup table and others, then using USERRELATIONSHIP in the CALCULATE formulas.

[amurph43]

I have revised the original post with updated (sanitized) sample data and some screenshots of what I'd expect.

[Anonymous]

Hi @amurph43 

Can you provide detailed sample data and the results you expect? So that I can help you better. Show it as a screenshot or excel. Please remove any sensitive data in advance.

^{Best Regards,}

^Jayleny

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