Forum Discussion
Helper IBroken relationship after RS refresh
Here is my workaround:
Originally my fact table date was a string like '2021-09-06T01:35:22Z'
I would do a transform to date/time, then a transform to Date
This was fine until Report Server did the refreshing
I've replcaed this double step with
Split '2021-09-06T01:35:22Z' by delmiter T, change to Date
This now works when RS Server refreshes.
Using other Power Query transforms or functions to date only just didn't work.
Super UserIt's highly likely that the relationships are fine and that this is actually a data issue. This issue is actually happening on both the second and third columns of your output. It could be a difference in the language settings between the client and server causing the server to parse the dates differently or it could be a difference in the timezone settings causing a shift in datetime values. It depends on what your raw data looks like and what sort of transformations your are doing. Are you able to isolate this down to a handful of rows of sample data so that you can easily compare the differences between your data sets?
Helper Iyep thanks for pointing me in that direction.
my date fields in both tables are datatype of Date only (as opposed to date/time)
When I right click a date entry - copy - paste to notepad: I get a timezone in my fact table date field which wasn't there prior to getting refreshed on the report server.
Report Server fields just after being saved/overwriten by PowerBI-RS
fact: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)
date: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)
Report Server fields after performing a refresh
fact: Thu Sep 09 2021 14:00:00 GMT+1000 (Australian Eastern Standard Time)
date: Thu Sep 09 2021 00:00:00 GMT+1000 (Australian Eastern Standard Time)
This is confusing as I thought converting data type to a date only would strip out time & timezone info