Issues updating calculated tables on BI service

[Caelan]

Hi, I have a Sharepoint list I'm pulling to BI. I'm creating 4 calculated tables with basic filters from the one list. When I refresh in app, the calculated tables all filter correctly, but when I update on service, the tables all filter incorrectly and calculated columns calculate incorrectly. I appreciate there are various other ways I could achieve the same outcome that may get around whatever the problem is, but I would like to understand why this isn't working in case I have similar issues in future. Below is an example of one of the dax formulas I'm using to filter a table.

---------------

Incomplete - North lay trustee =

CALCULATETABLE

(Approvals,Approvals[EmailedtoLloydsby]=Blank(),

Approvals[Section]="North",

Approvals[IsthereaLayTrustee?]="True",

Approvals[Approved]="Approved",

DATEVALUE(Approvals[Datetoaction])<Today())

---------------

and below is an example of a calculated column I'm using.

----------

Working days elapsed = NETWORKDAYS(

'Incomplete - North lay trustee'[Created],Today()-1,1,Sheet1)

--------------

Both work in app, neither in service refresh. Anyone any idea why that would be?

[Anonymous · Accepted solution]

Hi @Caelan ,

Please have a try.

Ensure that the data types for your columns in Power BI Desktop match the expected data types in the Power BI Service. Mismatches in data types can cause calculations to behave differently.
Power BI Service operates on UTC time by default. If your function is being used, keep in mind that it might be returning a different date in the service than in your local Power BI Desktop.Today()

For the CALCULATETABLE formula:
Make sure that is interpreted correctly in the service context.Today()
Verify that is yielding the expected results after the data type checking.DATEVALUE(Approvals[Datetoaction])

Solved: timezone corrections on power BI service not the s... - Microsoft Fabric Community

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Caelan]

Deleted my old reply as found the issue as soon as I'd posted it! It was as you describe a mismatch in column classification. I'd imported a yes/no as text. Desktop was fine with it but apparently service not. Thank you so much for your help.

[aj1973]

Hi @Caelan 

Calculated Columns and Tables or not always welcomed in the service. Both needded to be avoided as much as possible. Hope this can help

Avoiding circular dependency errors in DAX - SQLBI

Understanding circular dependencies in DAX - SQLBI