Forum Discussion
Auto Refresh on Power BI Service changes my chart values
Hey Community,
I have an issue with 2 charts in my dahsboard which is from MySQL database, these 2 charts has the below formula.
I have the following formulas to calculate the response time and resolution time:
Avg_First_Response_Time_Per_Owner =
AVERAGEX(
FILTER(
'ORG1 HD_TICKET',
NOT(ISBLANK('ORG1 HD_TICKET'[First_Response_Duration])) -- Exclude tickets with blank duration
),
'ORG1 HD_TICKET'[First_Response_Duration] -- Calculate average for first response duration
)
First_Response_Duration =
DATEDIFF(
'ORG1 HD_TICKET'[FirstResponseTime], 'ORG1 HD_TICKET'[TIME_OPENED],
HOUR
)
ResolutionTime =
AVERAGEX(
FILTER(
'ORG1 HD_TICKET',
'ORG1 HD_TICKET'[HD_STATUS_ID] = 2 && 'ORG1 HD_TICKET'[HD_QUEUE_ID] = 1
),
DATEDIFF('ORG1 HD_TICKET'[TIME_OPENED], 'ORG1 HD_TICKET'[TIME_CLOSED], HOUR) -- Change MINUTE to HOUR or DAY if needed
)
When I publish and refresh everytime the report from Power BI desktop , the dashboard report I see the values for Response Time By hours and Resolution Time by hours they are accurate and the formula is correct.
However, in PowerBI service once I selected the semantic model and clicked on Refresh now.
The values of the same charts change to negative numbers for some owners on the Response Time by Hours and thousands of numbers for the resolution time by hours chart:
What am I doing wrong in this?
Hi Anonymous, The issue is now resolved. I tried the method again from the YouTube video, by following everything on this video except for one of the steps where I found out in the comments section that I needed to replace the step "DateTimeZone.ToLocal" to "DateTimeZone.SwitchZone-([UTC-ColumnName_UTC],0).. To always display the information in the time zone it was collected. Thanks!
10 Replies
The Power BI Service runs on UTC.
That is a good point. Can I change that to EST?
You should not. Instead, make sure all your datetime values are in UTC as well, and leave it up to the users' browsers to convert to the local timezone.
SowmiSakthi21 Can you check whether you have any page level or visual level filters applied ? Also try turning off auto time intelligence feature, publish and refresh the model.
Thanks,
Jai
Yes we do have a page level filter for each page. How does that interfere with this? So we have one page that filters for the month and another page for the week.
Hi, SowmiSakthi21
May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
If it hasn't been resolved yet, please share more details about the issue you're having and we'll do our best to help you solve the problem you're having.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous, the issue hasn't been resolved yet, we are waiting for a reply from our last post. Scroll down to this post:
I also tried to do the method from the below Youtube video, but that did not work out:
https://www.youtube.com/watch?v=M1zquwmpnZE
Thanks for your help,
Best,
Lethi
Hi Anonymous, The issue is now resolved. I tried the method again from the YouTube video, by following everything on this video except for one of the steps where I found out in the comments section that I needed to replace the step "DateTimeZone.ToLocal" to "DateTimeZone.SwitchZone-([UTC-ColumnName_UTC],0).. To always display the information in the time zone it was collected. Thanks!
