Divergent numbers in the average chart in Power BI website after semantic model update

[Bertz]

Hello everyone,

I’m currently facing an issue with the published page of one of my dashboards.

When I publish the page, the numbers displayed on the website reflect what is shown in the application. However, when I update the semantic model, the numbers in certain charts change and completely diverge from what they should be.

In this charts, I use the average of a calculated column, where the calculation is theoretically simple:

Calc_TempoTotal =

IF(

    OR('DCC Activities list'[Status]="Closed",'DCC Activities list'[Status]="Document Published"),

    'DCC Activities list'[Modified]-'DCC Activities list'[Created],

    BLANK()

)

In the power BI application, when I update the data, the results are correctly returned. In fact, when I republish the dashboard, the chars appear as expected. However, once again, when the semantic model is updated, the discrepancy returns.

If anyone has faced a similar issue and managed to solve it, please share your solution here so I can stop racking my brain over this problem and focus on other problems!!!

[Poojara_D12 · Accepted solution]

Hi @Bertz 

The issue arises because calculated columns in Power BI are only evaluated when the dataset is refreshed, not dynamically like measures. When you update the semantic model, the calculated column Calc_TempoTotal is recalculated, potentially leading to discrepancies in the report, especially when aggregating values like averages. Additionally, differences in data processing between Power BI Desktop and the Power BI Service can cause inconsistencies. A better approach is to convert the calculation into a measure, as measures are calculated dynamically based on the current filter context. This ensures that the average duration is always correct, regardless of when the dataset is refreshed. Also, verifying that the Created and Modified columns have the correct DateTime format in Power Query can prevent unexpected errors. Checking the dataset refresh history in the Power BI Service will help identify potential refresh failures. Finally, after making these changes, republishing the report and validating the results should resolve the issue.