Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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.
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.
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 |
Hi!
I already have an active relationship between de datetable and the closedate, but not sure how I should use the datediff.
@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.
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.
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |