Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
User | Count |
---|---|
44 | |
27 | |
21 | |
15 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |