Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
robm135
Helper I
Helper I

Broken relationship after RS refresh

Hi I have an issue where resfreshing my data on Report Server breaks one of my datasets relationships to the date table.

 

The relationship is ok when refreshing the data in PowerBI desktop RS.

The relationship is ok when saving or overwriting the report to RS. 

The relationship breaks after any scheduled refresh on RS. (Refresh is reported successfull) 

 

e.g. goes from this:

robm135_0-1631090413064.png

to this:

robm135_1-1631090448059.png

 

I've gone into options and turned off these options

robm135_2-1631090569730.png

 

 

Running RS Jan21

1 ACCEPTED SOLUTION
robm135
Helper I
Helper I

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. 

View solution in original post

3 REPLIES 3
robm135
Helper I
Helper I

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. 

d_gosbell
Super User
Super User

It'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?

yep 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 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors