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,
Im a noob in Power BI. Just started about 2 months ago on and off.
I have data for closed tickets. Each line is unique ticket ID with created date time, closed date time, location code.
I would like to study the distribution for count pending tickets for each new tickets per location.
below is the sample data and intended collumns are the last 2.
Appreciate some help
TT_NUMBER | CREATED_DATE | CLOSED_DATETIME | LOCATION CODE | Pending Tickets (Overall) | Pending Tickets (Per Location) |
1-34415354673 | 1/6/2020 9:22 | 3/7/2020 9:59 | S003 | 0 | 0 |
1-22933800909 | 1/6/2020 10:25 | 1/7/2020 17:38 | S004 | 1 | 0 |
1-34426852255 | 1/6/2020 11:37 | 1/7/2020 12:43 | S001 | 2 | 0 |
1-34429503918 | 1/6/2020 13:14 | 10/7/2020 20:35 | S005 | 3 | 0 |
1-34429366814 | 1/6/2020 13:42 | 7/7/2020 14:51 | S001 | 4 | 1 |
1-22935228732 | 1/6/2020 14:12 | 6/7/2020 11:20 | S001 | 5 | 2 |
1-34439286922 | 1/6/2020 20:18 | 10/7/2020 20:10 | S005 | 6 | 1 |
1-34439415415 | 1/6/2020 20:29 | 3/7/2020 14:41 | S001 | 7 | 3 |
1-34441808655 | 1/6/2020 23:37 | 6/7/2020 20:32 | S003 | 8 | 1 |
1-34971269691 | 1/7/2020 0:01 | 1/7/2020 16:19 | S005 | 9 | 2 |
1-34971954911 | 1/7/2020 0:06 | 1/7/2020 10:45 | S002 | 10 | 0 |
INC0002105223 | 1/7/2020 0:11 | 12/7/2020 8:33 | S006 | 11 | 0 |
INC0002105242 | 1/7/2020 0:14 | 12/7/2020 8:33 | S007 | 12 | 0 |
1-23056555127 | 1/7/2020 0:15 | 1/7/2020 17:24 | S008 | 13 | 0 |
1-34972860319 | 1/7/2020 0:15 | 4/7/2020 5:32 | S006 | 14 | 2 |
1-34972542750 | 1/7/2020 0:16 | 1/7/2020 20:16 | S001 | 15 | 4 |
INC0002105274 | 1/7/2020 0:24 | 12/7/2020 8:33 | S006 | 16 | 1 |
INC0002105275 | 1/7/2020 0:24 | 5/7/2020 1:57 | S006 | 17 | 2 |
1-34970507931 | 1/7/2020 0:24 | 1/7/2020 16:24 | S004 | 18 | 1 |
1-23056048995 | 1/7/2020 0:27 | 1/7/2020 12:44 | S009 | 19 | 0 |
Solved! Go to Solution.
Hi, @marlinajamal
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create two calculated columns as below.
Pending Tickets (Overall) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
Pending Tickets (Per Location) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @marlinajamal
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create two calculated columns as below.
Pending Tickets (Overall) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
Pending Tickets (Per Location) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Tq so much. You given me a starting point.
The CLOSED_DATE TIME should also be one of the parameters, so i add it to the formula and edit the 3rd ticket (1st for S001) CLOSED DATE TIME to be closed before the creation of the 5th tickets for S001 to make sure the formula works.
and it works!
The formula :
Pending Tickets (Overall) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE]) &&
'Table'[CLOSED_DATETIME]>EARLIER('Table'[CREATED_DATE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
Pending Tickets (Per Location) =
var result=
CALCULATE(
DISTINCTCOUNT('Table'[TT_NUMBER]),
FILTER(
ALL('Table'),
'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
'Table'[CLOSED_DATETIME]>EARLIER('Table'[CREATED_DATE])&&
'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
)
)
return
IF(
ISBLANK(result),
0,
result
)
The result:
So thank you so much for your help!
Been stuck for a few weeks tbh
Edited file here
Pending Tickets Backlog Count for Distribution Analysis_edited.pbix
@marlinajamal , What is the expected output?
You can create date like
CREATED_DATE Date = [CREATED_DATE].date
CLOSED_DATE = [CLOSED_DATE].Date
And join them with date table and One active join. One inactive join. You can use inactive join with help from userelation. Refer:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
@amitchandak , the output is calculated collumn that gives value as per the last 2 collumn.
Pending Tickets (Overall) - when no filter selected
Pending Tickets Per State - when State Code selected as filter
@marlinajamal , To me first column (out of 2 last) seems like an index column. It is not going up down based on some logic. What is the logic for that?
Second column. You can not create a column that can use slicer values.
the logic or the formula for the first collumn is
count of tickets still opened at the time of creation of new ticket. Any tickets with Created Date Time older AND Closed Date Time newer than than the reference ticket's Created Date Time.
For Collumn (Pending Tickets Overall)
for line one, there are no other tickets created before hence, 0.
For line 2, theres 1 ticket open previously(older creation date) and not yet closed(newer closed date), hence 1.
and so on.
maybe the sample is not really good as it didnt demonstrate the pending tickets count may go down if there's ticket closed before the creation of the reference ticket.
Hope this clarify.
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 |
---|---|
110 | |
98 | |
79 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |