cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

Open Tickets at any point in time

I am creating a report on How many Open Tickets, Closed Tickets and Open at the end of Year, Qtr, Month, Date (at any point in time using Date Hierarchy).

I was using the below codes for many projects and it was working fine but now it is not working in one of my data and I have no clue on whats wrong with data or the query or etc., 😂

I have Tickets table and DateDim (calendar) table with Active relationship between Datedim.Date and Tickets.CreationDate, Inactive relationship between Datedim.Date and Tickets.ResolutionDate

Opened = COUNTROWS(Tickets)  => Output is Correct
Closed = CALCULATE([Opened],USERELATIONSHIP(Tickets[ResolutionDate],DateDim[Date]))  => Output is Correct

Below Measure is show the wrong values.

Active =
VAR MaxDate = MAX(DateDim[Date])

VAR ActiveTickets =
CALCULATE(
COUNT(Tickets[ID]),
ALL(DateDim),
Tickets[CreationDate] <= MaxDate,
ISBLANK(Tickets[ResolutionDate])
|| Tickets[ResolutionDate] > MaxDate
)

RETURN
ActiveTickets

From Qtr 3 Jul the count of Active is showing wrong. (It should be 127 from Qyr End + 1291 Jul Opened - 30 Closed Jul = 1388. Similarly for all Years, etc., I have no clue.

( I have attached the PBIX and Data in Excel in this LINK ). Kindly help.

1 ACCEPTED SOLUTION
Employee

The use of USERELATIONSHIP forces the relationship to the ResolutionDate column so your filter on CreationDate column isn't working as you'd expect.  To address it, the two number can be calculated separately in variables and then subtracted.

Active 2 =
VAR maxdate =
MAX ( DateDim[Date] )
VAR openedtilnow =
CALCULATE ( COUNT ( Tickets[ID] )ALL ( DateDim ), DateDim[Date] <= maxdate )
VAR closedtilnow =
CALCULATE (
COUNT ( Tickets[ID] ),
ALL ( DateDim ),
DateDim[Date] <= maxdate,
NOT ( ISBLANK ( Tickets[ResolutionDate] ) ),
USERELATIONSHIP ( DateDim[Date], Tickets[ResolutionDate] )
)
RETURN
openedtilnow - closedtilnow

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

6 REPLIES 6
Employee

The use of USERELATIONSHIP forces the relationship to the ResolutionDate column so your filter on CreationDate column isn't working as you'd expect.  To address it, the two number can be calculated separately in variables and then subtracted.

Active 2 =
VAR maxdate =
MAX ( DateDim[Date] )
VAR openedtilnow =
CALCULATE ( COUNT ( Tickets[ID] )ALL ( DateDim ), DateDim[Date] <= maxdate )
VAR closedtilnow =
CALCULATE (
COUNT ( Tickets[ID] ),
ALL ( DateDim ),
DateDim[Date] <= maxdate,
NOT ( ISBLANK ( Tickets[ResolutionDate] ) ),
USERELATIONSHIP ( DateDim[Date], Tickets[ResolutionDate] )
)
RETURN
openedtilnow - closedtilnow

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper V

Brilliant to the core. You are awesome...! Thank you so much @mahoneypat

Helper V
Super User

Hi,

My proposed solution will not work here because i want to create one row for date between the 2 dates (Creation date and Resolution date).  When i do that the number of rows balloon to 10 milltion rows.  Someone else will help you.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

Thank you @Ashish_Mathur for trying.

Helper V

Kindly somebody help...

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors