Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
@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
I was not aware of the function USERRELATIONSHIP and making both relationship inactive.
Thanks again for your help!
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |