Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

IDCreateClose
ID12020-01-182020-01-25
ID22020-01-192020-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:

 

DateOpen
2020-01-170
2020-01-181
2020-01-192
2020-01-202
2020-01-212
2020-01-222
2020-01-232
2020-01-242
2020-01-252
2020-01-261
2020-01-270
2020-01-280

 

This is the measure that I used, but it is only showing the 25th and 26th of January (both 1 result).

 

Backlog =
VAR CurrentDate = MAX(DateTable[Date])
VAR Result =
CALCULATE(
COUNTROWS(FactTable),
ALL(DateTable),
DateTable[Date] <= CurrentDate
)
RETURN
Result

 

I also found similar posts that had sort of the same questions, but I can't get it to work.

 

Regards,

Michael

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

pranit828
Community Champion
Community Champion

HI @Anonymous 

Use DATEDIFF(create,close,day) 

to get the difference in days.

 

Create a relationship on both tables and choose date column from calander table on axis of the chart.





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

Hi!

I already have an active relationship between de datetable and the closedate, but not sure how I should use the datediff.

darentengmfs
Post Prodigy
Post 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.

Anonymous
Not applicable

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?

 

@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.

Anonymous
Not applicable

Hi,

 

if I understand you correctly this wil give me a count of the create dates, but when an ID is open for more than one day, the count will be incorrect. It has to count until the closedate. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.