Create Date and Resolved Date totals on bar chart

[nleuck]

Hello All,

I'm working on a report that shows Help Desk ticket activity. I'm trying to show total tickets created and total tickets resolved on a bar chart. I have two tables; one for Tickets and the other is a Date table. I have a relationship between the two tables Created Date from Tickets and Date from Date. Here is what the chart looks like if I use that relationship:

The issue with this chart is the Total Resolved only shows the total resolved that were created in the same month. That's because of the relationship I have from the two tables Tickets and Date (Created Date and Date). The X-axis Month Year comes from my Date table.

Now if I change the relationship from Created Date and Date to Resolved Date and Date here is what I get:

As you can see my Total Resolved is correct but my Total Created is not. So my only solution is to create two different bar charts; one for Total Created and the other Total Resolved.

I would like to show them on one chart for better comparison but having two different dates gets complicated. Not all tickets created will be resolved the same month, so most months will have a higher total resolved than created which is fine.

Does anyone have any solutions or ideas?

Thanks!

[amitchandak · Accepted solution]

@nleuck , Can you share the formula you are using?

Please check

Both Dates do not have timestamp

You have joined the Both Created and resolved with the same date in the date table

Userelation has been used properly

Example of userelation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[nleuck]

@amitchandak 

I was not aware of the function USERRELATIONSHIP and making both relationship inactive.

Thanks again for your help!

[parry2k]

@nleuck You have to have a relationship with your date table on both the create and resolve date in your ticket table. One will be an inactive relationship which is fine, add following measures, assuming you have an inactive relationship with the resolved date

Create Count = COUNTROWS ( Table )

Resolve Count = CALCULATE ( [Create Count], USERRELATIONSHIP ( DateTable[Date], Table[ResolveDate] ) )

Read more about USERRELATIONSHIP  function here.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!