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.
HI Team,
I'm developing report where I want to analyze the open/created tickets(count) versus closed tickets(count) by ticket status by month,Qtr,Year .
Today I can filter (closed tickets or opened tickets) in my tickets system by the date using two separated measures. I'm not able to get tickets which have the same date/period of "Creation_Date" and "LatestUpdated_Date" according the calendar dim table.
Created Tickets : Using Active join enabled between creation_date and calendar dim table.
Closed Tickets: Here is my problem. Not able to figureout the no.of tickets in period based on the inactive join between LastUpdated_Date and calendar dim table. Even tried USERELATIONSHIP, but no luck. Please help.
Tickets_Details Table:
Ticket Number | CREATION_DATE | LastUpdated_Date | Resolution Status |
66527 | 1/20/2019 0:00 | 1/20/2019 0:00 | DRAFT |
66528 | 1/20/2019 0:00 | 3/20/2019 0:00 | PENDING APPROVAL |
66529 | 1/20/2019 0:00 | 1/20/2019 0:00 | PENDING APPROVAL |
66530 | 1/20/2019 0:00 | 1/20/2019 0:00 | UNDER REVIEW |
66531 | 1/20/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
66532 | 1/20/2019 0:00 | 3/20/2019 0:00 | DRAFT |
66533 | 1/20/2019 0:00 | 1/21/2019 0:00 | IMPLEMENTED |
66534 | 1/20/2019 0:00 | 1/20/2019 0:00 | UNDER REVIEW |
66535 | 1/20/2019 0:00 | 2/21/2019 0:00 | UNDER REVIEW |
66536 | 1/20/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
66537 | 1/20/2019 0:00 | 1/20/2019 0:00 | DRAFT |
66538 | 1/20/2019 0:00 | 1/20/2019 0:00 | DRAFT |
66539 | 1/20/2019 0:00 | 1/20/2019 0:00 | SCREEN |
66540 | 1/20/2019 0:00 | 1/20/2019 0:00 | UNDER REVIEW |
66541 | 1/20/2019 0:00 | 1/20/2019 0:00 | UNDER REVIEW |
66542 | 1/20/2019 0:00 | 1/20/2019 0:00 | DRAFT |
66543 | 1/21/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
66544 | 1/21/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
66545 | 1/21/2019 0:00 | 3/28/2019 0:00 | UNDER REVIEW |
66546 | 1/21/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
66547 | 1/21/2019 0:00 | 1/21/2019 0:00 | DRAFT |
66548 | 1/21/2019 0:00 | 2/21/2019 0:00 | WITHDRAWN |
66549 | 1/21/2019 0:00 | 1/21/2019 0:00 | DRAFT |
66550 | 1/21/2019 0:00 | 1/21/2019 0:00 | DRAFT |
66551 | 1/21/2019 0:00 | 1/21/2019 0:00 | SCREEN |
66552 | 1/21/2019 0:00 | 1/21/2019 0:00 | UNDER REVIEW |
Regards
SRRY
Hello,
Did you use USERELATIONSHIP in the following way;
Measure = CALCULATE ( COUNTROWS ( Tickets_Details ), USERELATIONSHIP ( 'Calendar'[Date], Tickets_Details[LastUpdated_Date] ) )
It should work...
Hi, Thanks for the reply. my problem here is that when I add Resolution Status, the no.of tickets created showing incorrect as data is spliting based on resolution status.
Basicaly, I want see how many tickets created,closed in period(month,Qtr,Year). Staus is also needed in drill down(I mean status should be available along X-axis).
I'm expecting visual as below.
Status grouping as below.
Closed ={Closed,Rejected,withdrawn}
OPened = which are not comes under Closed (as above).
In below visual I have taken Creation Date so, closed tickets showing incorrectly.
please help me here.
@avanderschilden wrote:Hello,
Did you use USERELATIONSHIP in the following way;
Measure = CALCULATE ( COUNTROWS ( Tickets_Details ), USERELATIONSHIP ( 'Calendar'[Date], Tickets_Details[LastUpdated_Date] ) )It should work...
Graph1:
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |