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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Calculated column works in Power BI desktop but not Power BI service. Visual won't load.

I have a pie chart visual in my report that uses a calculated column as one of the fields. Up until now it stopped working in Power BI service. Nothing has changed on the report and it has always worked in the past. The visual works fine in Power BI desktop but when published to Service it gives me an error. Any ideas why it would stop working suddenly?

 

chang_B_0-1676468657256.png

 

Status: Accepted

Hi @chang_B 

It’s a known issue . The engineers are actively working on this issue and I will come back with an update if there is any progress.

 

Best Regards,
Community Support Team _ Ailsa Tao

Comments
irislarin
Regular Visitor

How will this be resolved?

 

JamesG1978
Regular Visitor

@irislarinto me it looks like a bug or change to the Power BI Service.  My example was working fine on Friday morning and not on Saturday.

 

The original version of report still works fine Power BI Desktop.

 

@v-yetao1-msftcan we get an update please?

bginders
Regular Visitor

I am getting exactly the same issue, we have several composite datasets, which link to the main data model using direct-query. since today most columns using the TODAY() function or FORMAT() are resulting in the same error as the OP.  

When checking the formulas locally I see the error as seen in the below screenshot

bginders_0-1676910200657.png

 

Please note: these data models have not been changed for months and have worked fine up until this point.

 

 

greggle24
Regular Visitor

I have been encountering the same issue as the others. Believe I first noticed it on Friday 17th Feb. Report had been working fine for months until issues began.

 

Connection via DirectQuery to several Azure Analysis Services models. The error is happening on our date dim table (image attached) which contains 5 calculated columns, all of which are erroring in the same manner as image below. Appears others are reporting issues with dates. All 5 columns use TODAY() function. The error occurs in the following sequence:

  1. No errors when connecting/refreshing in PBI Desktop
  2. Publish to PBI Online - still works fine
  3. Refresh dataset manually - connection breaks with the attached message
  4. Equally, when deploying report/dataset via deployment pipeline, connection will also break.
    greggle24_1-1676939135255.png

     

Details:

Activity ID: c4b7c309-a835-4b06-bcf1-a6d34cd5281f
Request ID: bc99c21a-82ac-49af-b045-d71053651562
Correlation ID: 5caf2d1f-b0ac-b08d-193c-f8c36ea9ff6a
Time: Mon Feb 20 2023 19:24:44 GMT-0500 (Eastern Standard Time)
Service version: 13.0.20105.47
Client version: 2302.2.12402-train
Cluster URI: https://wabi-us-east2-c-primary-redirect.analysis.windows.net/

 

Update:


I tested replacing the TODAY() function with a hard coded date using DATE() and this eliminated the issue. Does not fix my issue since I need a dynamic reference to today...

 

For me at least then, the issue is caused when refreshing a dataset in direct query mode that has a calculated column containing the TODAY() function. UTCTODAY() and NOW() also does not work.

bginders
Regular Visitor

Hi @Ailsa-msft,

To answer your request for info, I can confirm that no visuals work with the affected calculated columns.  

 

As per other comments here, I can confirm the following steps to reproduce the problem:

  1. Create a direct query dataset (D1) linked to a dataset which uses import (D2) and is already in the PBI Service.
  2. Add a calculated column in D1, which either uses the Today() or Format() function against columns from D2
  3. Add a table visual element and you will see the column populates data as expected in Power BI Desktop.
  4. Publish D1 to a Workspace and refresh D1 
  5. Open the report in the PBI Service and you will see the visula does not render, only providing an error (See Below).
     bginders_0-1676969337583.png
  6. Download D1 again, and open in Power BI Desktop, you will see the earlier column now shows an error (see below image)

    bginders_1-1676969606382.png
  7. Now refresh D1 again in Power BI Desktop and you will see the error dissapears and the column renders as expected. 

    bginders_2-1676969752466.png

Each time the datasets are uploaded the errors appear, which demostrates a problem in the Power BI Service. Also the fact all the now error columns worked last week but not this week suggests a change to the Power BI Service during the last weekend.

 

 

Maieev
Resolver I

Hi there,

 

same problem here. Everything worked fine until this Monday. I didn't change anything. I have calculated column and measures based on DQ fields and it stopped working. All other stuff works. When I remove calculated columns and measures it works so it must be something related to that.

 

Desktop:

Maieev_0-1676970833435.png

 

Service:

Maieev_1-1676970864914.png

 

Bug:

Maieev_2-1676970880691.png

 

As above, calculations with TODAY() formula.

 

ttoews
Regular Visitor

Hi,


we have the same problem: a calculated column in a DQ Table (AAS) no longer works in the service -> broken visualizations. As reported above, the TODAY() function is part of the calculation. In desktop mode everything works as expected.

 

Best regards

Thomas

 

 

TE
Frequent Visitor

Same issue here with my composite mode. Worked fine on Friday, but not this week.

 

"Couldn't load the data for this visual

The query referenced calculated column 'Contracts'[Estimated contract financed date] which does not hold any data because there is an error in its expression.
Please try again later or contact support. If you contact support, please provide these details.

Activity ID dacf12ac-8122-47ee-a9a8-34907c5f9aed
Request ID 9290581f-3188-4915-8b88-867e58c72f8a
Correlation ID 254d5fe4-960b-9aeb-90b4-73990081f8e9
Time Tue Feb 21 2023 12:28:49 GMT+0200 (Eastern European Standard Time)
Service version 13.0.20105.47
Client version 2302.2.12402-train
Cluster URI https://wabi-europe-north-b-redirect.analysis.windows.net/"

BenGinders
Regular Visitor

Hi, @v-tangjie-msft@v-xiaoyan-msft,  @v-yetao1-msft,

 

Could you please help to progress a fix for this, I believe all the requested additional infobhas been provided, if not please let me know and I will surely provide any additional info.

irislarin
Regular Visitor

 @v-tangjie-msft@v-caitlyn-mstf,  @Ailsa-msft  when can we expect a fix?