Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Kindly somebody help...
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
97 | |
38 | |
37 |
User | Count |
---|---|
153 | |
122 | |
75 | |
73 | |
64 |