Data refresh is giving unpredictable and incorrect results

[Vincitori]

Hi all,

I have a very strange behaviour in one of my Power BI reports. In short we have the following situation:

- Report is developed on desktop using imports from an Azure database. Within the model several refrence tables are generated based on detail data using distinct functions, e.g. customer:

    Source = Table.Distinct(Table.SelectColumns(BasisRegistraties, {"Debiteur", "SAPNummerDebiteur"})),
    #"Added Conditional Column" = Table.AddColumn(Source, "Boxhouder", each if [SAPNummerDebiteur] = "1000078337" then "Name of the company" else "Other name"),
    #"Added Index" = Table.AddIndexColumn(#"Added Conditional Column", "dimDebiteurID", 1, 1, Int64.Type)

In a later step the fact table is created using the added indexcolumn as linking key for the relation

- On the server the connection is using a gateway to connect to the database

When refreshing the data on my local machine, I get the results I expect, so that's without the gateway.

After publishing to the server and refreshing the data I get unpredictable results. Every time I refresh the data, it is showing different results. I downloaded the pbix from the server and the relations between the fact table and the dimensions are incorrect. When I refresh on the local desktop, everything is fine again.

I also created a new local duplicate of the existing report and published this one, same issues occur.

The strange parts of it:

- Every refresh on the server gives a different result, although the underlying database has not been changed.

- We also have a test database environment, with more or less the same data. Switching the database (I'm using a parameter for this) to the test and refreshing on the server, also using the gateway connection, is returing the expected result every time.

I'm really getting crazy of this issue...... Has anyone faced the same issues or does anyone have tips or tricks that can help?

Any help is appreciaated!!!!

Regards,

André

[Ruchita]

I was also doing a similar thing and similar error. I was creating an index col in the dimesion table and then linking the fact and dimesion tables on this index key. Instead, now I am creating a custom column by concatenating the 2 primary keys in both the tables. This custom column is now used to create the relationship between the 2 tables. This solved the issue.

[Ruchita]

Hi,

Did you find solution to this problem? I am having the same issue. Every refresh from Power BI service giving different figures.

[Vincitori]

My issue was solved by cleaning and trimming the joined elements (Text.Clean(Text.Trim([Field])) in both tables

[Ruchita]

Thanks Vincitori for replying back.

I also tried this. I cleaned and trimmed the columns which are used for joins in both the tables. But still the problem persists. I have raised a support ticket with Microsoft and its been more than 20 days, they haven't been able to find any solution.

[Vincitori · Accepted solution]

I think I've found the solution for the above issue!

As I'm deriving the dimensions from the master table, I was expecting the items, I use to join between the fact table and the dimension, to be the same.

I suspect one of the elements to contain special characters and/or leading/trailing spaces, so I inserted in the power query 2 steps to trim and clean the text strings and it looks like this solved the issue!

[v-stephen-msft]

Hi @Vincitori ,

I found a similar post that might help you:

Solved: power bi desktop shows different data than in powe... - Microsoft Fabric Community

Are you saying that your data model relationships on Power BI Service are different from yours? Did the relationship change after uploading?

If you can, please provide some screenshots for demonstration, remember to protect your privacy.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Vincitori]

Hi Stephen,

The data model relationship stays in place, but the data within the relationship columns is changed, e.g. in the original data the reference is to customer A, but after refresh on the Power BI service it is to customer B and this looks randomly, because next time I refresh on the server it can also be Customer C or D.... 

I have a very simple model, see below, based on one date table and data table, used as base for the other tables. The tables on the left and right lower corner are all derived from the data table using a table.distinct and a surrogate key using the index function of power query.

The fact table in the middle is using the surrogate keys to link to the dimension, so in fact I'm more or less creating a star scheme from a single table.

When looking at the data that is refreshed on the Power BI service, it looks like the relationship from the fact table is created to the dimensions before the dimensions are refreshed.