Forum Discussion
Count days open ticket
Hi All,
I'm currently struggeling with calculating the open tickets per day and I feel like I am overcomplicating things.
The simplified dataset is as following:
| ID | Create | Close |
| ID1 | 2020-01-18 | 2020-01-25 |
| ID2 | 2020-01-19 | 2020-01-26 |
Furthermore I have a calendar table that is connected to the close date.
I want to create a linechart that shows the amount of tickets open on a particular day during the years.
so this is what it probably has to look like:
| Date | Open |
| 2020-01-17 | 0 |
| 2020-01-18 | 1 |
| 2020-01-19 | 2 |
| 2020-01-20 | 2 |
| 2020-01-21 | 2 |
| 2020-01-22 | 2 |
| 2020-01-23 | 2 |
| 2020-01-24 | 2 |
| 2020-01-25 | 2 |
| 2020-01-26 | 1 |
| 2020-01-27 | 0 |
| 2020-01-28 | 0 |
This is the measure that I used, but it is only showing the 25th and 26th of January (both 1 result).
I also found similar posts that had sort of the same questions, but I can't get it to work.
Regards,
Michael
Hi All,
I found my solution in this post:
https://community.powerbi.com/t5/Desktop/Count-open-support-case/m-p/1085834#M502883
What I did was a one to many relationship between my date (1) and facttable (many) and used the following DAX:
Open Cases =
CALCULATE (
COUNTROWS ( FactTable ),
FILTER (
ALL ( FactTable ),
FactTable[Create] <= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
&& (
FactTable[Close] >= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
|| ISBLANK ( FactTable[Close] )
)
)
)Many thanks for the support guys!
Regards,
Michael
8 Replies
Hi All,
I found my solution in this post:
https://community.powerbi.com/t5/Desktop/Count-open-support-case/m-p/1085834#M502883
What I did was a one to many relationship between my date (1) and facttable (many) and used the following DAX:
Open Cases =
CALCULATE (
COUNTROWS ( FactTable ),
FILTER (
ALL ( FactTable ),
FactTable[Create] <= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
&& (
FactTable[Close] >= SELECTEDVALUE ( tbl_dCalendarTable[Date] )
|| ISBLANK ( FactTable[Close] )
)
)
)Many thanks for the support guys!
Regards,
Michael
darentengmfsPost Prodigy
Anonymous
If you just want to create a line chart with a count of open tickets, I suggest that you reference/duplicate the table with the data. Then, you create a new relationship connecting the new table with the date table using the Created Date. Do the count on the new table instead of your current table and use that in your chart.
Hi darentengmfs
Thanks for the quick reply.
I want to avoid duplicating the table, because it is a facttable that contains more than a million rows.
Referencing would also be possible I suppose with userelationship, but can you help me a bit with the code?
- darentengmfs
Anonymous
Go to Power Query, right click on your current query and choose Reference.
Then, select your Created Date column, right click, and choose Remove Other Columns. Then under Transform tab, click Group By, do a count, and define your new column name. It will give you a count of the rows with the same date. You can then define the relationship using the Created Column and use the count as your value.
I don't know if this is overkill or not....
I create a new 'closed' column that is equal to the actual close date or the current date. Then:
#"Added Reporing Dates" = Table.AddColumn(#"LastStep", "DayByDay", each { Number.From([TaskOpen_dt])..Number.From([TaskCloseOrCurrent_dt])}),
#"Expanded To Day By Day" = Table.ExpandListColumn(#"Added Reporing Dates", "DayByDay")You will get a row for each date the ticket is open, grouping by the DayByDay column and counting the tickets may do it.