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.
Need help writing a measure to find the count of open tickets as a line in the attached report which can be filtered by "Team" and be able to drill down to a week or month hierarchy.
PBIX file uploaded to drive: https://drive.google.com/file/d/1wR7V-JjLN3jmLWEJ8ESffn9yAT8VQ-aF/view?usp=sharing
Solved! Go to Solution.
Very similar to what I discussed in : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Can you see if that can help
Formula like this
Open ticket = CALCULATE(COUNTX(FILTER(Ticket_list,Ticket_list[Created_Date]<=max(Calender_Date[Date]) && (ISBLANK(Ticket_list[Closed_Date]) || Ticket_list[Closed_Date]>MAX(Calender_Date[Date]))),Ticket_list[Ticket_Num]),CROSSFILTER(Ticket_list[Created_Date],Calender_Date[Date],None))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
I had to make a couple adjustments to your model. My output is the below -
With this you're able to quickly review which tickets below to what team, as well as see which tickets are still Open.
Some things I had to adjust -
1. Marked [Calendar_Date] as DateTable.
2. Changed [Ticket_Num] to a Text Format.
3. Adjusted Relationship between DateTable and [Ticket_List]
4. Created Calculated Column to apply a "Status": Open/Closed
5. Created Measure to calculate [Total Tickets] = DISTINCTCOUNT( Ticket_list[Ticket_Num] )
6. Created Measure to calculate [Total Open Tickets] = CALCULATE( [Total Tickets], Ticket_list[Status] = "Open" )
7. Created Measure to calculate [Total Closed Tickets] = CALCULATE( [Total Tickets], Ticket_list[Status] = "Closed" )
8. Created Table within Report to show records
9. Created Slicers within report to Slice by Team and Status
10. Created Card to display Values
11. Created Bar Chart to show Closed vs Open
Let me know if this is the route you're looking to take - If needed connect with me and I can walk you through the steps I took.
Also - Please mark as Solution and give me a thumbs up if you would like!
Enjoy!
Very similar to what I discussed in : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Can you see if that can help
Formula like this
Open ticket = CALCULATE(COUNTX(FILTER(Ticket_list,Ticket_list[Created_Date]<=max(Calender_Date[Date]) && (ISBLANK(Ticket_list[Closed_Date]) || Ticket_list[Closed_Date]>MAX(Calender_Date[Date]))),Ticket_list[Ticket_Num]),CROSSFILTER(Ticket_list[Created_Date],Calender_Date[Date],None))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
@amitchandak Appreciate your help. And the blog post is very helpful in my context. Your measure formula is exactly what I need.
One issue:
When I add date filter, "Open Ticket" count varies accordingly(as shown in photos). Is there a way to get the actual open ticket count from table data rather than filtered data? In other words, is there a way for this measure to ignore this specific filter?
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |