Data Still Not Refreshing on Service

[Anonymous]

Hi Everyone,

I continue to struggle with data updating on the desktop but not the service.  I posted a similar thread back earlier this month where I was able to resolve the issue by duplicating my data source file and referencing the new file, but this has stopped working again.

This relates to my total invoiced dollars in my report.  It works now up to June 17, but the data will not update by auto-refreshing or triggering a refresh in the service.  This time, one of my troubleshooting methods was to download the report from the service, and when I did the data was not refreshed after June 17.  However, if I manually update the file I downloaded fromt he service, the data comes in for June 17 up to today.  

I have read some comments online about issues caused by data type "any".  Do I need to go through and make all data text/number/date/etc?  I have a few custom column in my report that include changing data types, so going through these steps is a lot of work if it will not resolve my problem.

Any thoughts at all as to why I have to manually update this table would be appreciated!  

[Anonymous]

Hi Everyone,

This was one of the most frustrating experiences I have had with PBI, but it is resolved now, and I just want to share what caused my issue in case anyone else is ever in the same predictament I was in.

I was able to solve the issue when I started to rebuild my report.  I started with my two dimension tables (customer master and date table) and then my two fact tables, one with all invoices up to the end of 2020 and a second with invoices from Jan 1, 2021 and after, which were then appended in power query (PQ) into one summary fact table. 

After setting up the two tables in PQ, I went to set up my relationships and noticed that my summary fact table had the relationships set-up automatically to both dim tables correctly, but the second fact table (2021 and after) had only one relationship detected and created.  This made me wonder why the first table had two relationships created automatically, but the second ony had one, so I went back to a working version of the report from earlier in the year, and noticed it did not have any relationships between the second fact table and the two dim tables.  So I went back to my troubled report, noticed that there was one relationship in it as well between the second fact table and one of the dim tables and removed it, and that corrected my problem.  Since then, the report has been working.

I believe this issue was caused because I recently had to rename the datasource file for second fact table (2021 on after) and when I did that, I assume this relationship was created.  

Anyway, hopefully that helps someone.  This one incorrect relationship caused me many many hours of work and frustration.

[otravers]

Thanks for your post mortem, these learning experiences can be painful but that's how you get better. It's best to disable auto relationships in Power BI Desktop settings as they lead to situations like yours.

[otravers]

Create a simple table in your report where you have the day from the dimension table and the day from the invoices fact table. Do the rows past June 17 get assigned properly to the corresponding day in the dimension, or do they end up on a blank space (= no match between fact and dimension). Is the date in the fact table of date or datetime type?

[otravers]

Do you have a date dimension table in your model? If yes, what's the most recent day in it?

[Anonymous]

Hello again,

In my desktop version, I see the below,

and on the service I see the below.

I circled the last day that shows up in both, but it is off slightly.  I checked the invoice register and I do not have an invoice that matches the discrepancy amount.  

[otravers]

Looks like a matrix, not a table. Can you create a table where both the dimension and fact date columns are shown?

[Anonymous]

Good Day,

I have created a table in my desktop version and published it to the server.  I then extracted the data from both.  

What I see for the desktop on the last day the servic worked (June 17) is below, (note I hid customer information columns)

And for the service I have the below (customer details hidden again),

Two issues come to me when I look at this.  The first is that for the data in the desktop, time is included, although I am not sure if that could create the issue.  I also see an order that it is in the service but not the desktop (order 238931).  I believe this should not be there....

Thoughts?

[otravers]

It's hard to help if you're not going to provide the requested info:

- Is the date in the fact table of date or datetime type?

- Can you produce the requested table in Power BI, not Excel?

[Anonymous]

The data in the fact table is just "Date" type.  

The table below is from the desktop,

The table on the service is below.  It still only includes up to 06/17.

[Anonymous]

Hi Everyone,

I wanted to add one more piece of information....

Today I started a new report that is only my calendar table and my two invoice registers (which are appended in power query).  When I created this new report, it initially published with the proper data, e.g. sales up to June 26, but when it refreshed on the service, it again went back to June 17.  In this new report, I have only one Table visual, and no filters/slicers/etc, but still having the same issue.

[Anonymous]

Yes, I have a date table.  It goes out to June 30, 2024 (where I report out on forecast).  All forecast data is accurate and updates appropriately in the desktop and the service.

[otravers]

Do you have any errors spit out by the service when you're trying to refresh?

It is indeed better to assign a type to all your columns (default to text if they're fluctuating in nature). In fact if you were to move your ETL out of the dataset to dataflows, you would be forced to assign types to be able to save the dataflows.

[Anonymous]

I do not get any errors.  The report works refreshes and all data is correct, except for my invoiced dollars after June 17.  The invoiced dollars retains the data up to June 17.