Error in calculated column only in power bi service, not in power bi desktop

[Anonymous]

I have a report where I calculate whether a sensor has been activated in the last three days or not. Until today it worked perfectly, but now I get an error message in Power BI Service (NOT in Power BI Desktop, there it works just fine). 

Error message: 

Couldn't load the data for this visual
The query referenced calculated column 'Sensors'[IsInactive] which does not hold any data because evaluation of one of the rows caused an error.

DAX: IF(DATEDIFF(DATE(RIGHT(FIRSTNONBLANK(LiveData[Date]; LiveData[Date]); 4); MID(FIRSTNONBLANK(LiveData[Date]; LiveData[Date]); 4; 2); LEFT(FIRSTNONBLANK(LiveData[Date]; LiveData[Date]); 2)); TODAY(); DAY) > 3; "Inactive"; "Active")

The reason for not calculating the date field directly is that it is formatted as text in the data source and query format is direct query for that table. 

I use the updated version of Power BI Desktop. Any suggestions?

[v-qiuyu-msft]

Hi @Anonymous,

It looks like the issue happens when you access the report on Power BI service, right? There should be a detail information with RequestID and ClusterURl, please share these information. 

Please download the report from Power BI service then open the report in Power BI desktop, refresh the data, check if any error occurs. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

Activity IDb0bbb1d3-c167-4faf-849c-4cda5ca52ea5
Request ID17d41be3-9c9d-8f27-912f-d22d3aefbde1
Correlation ID5f4f66f1-b18f-07b7-3fd8-cb2904ce440d
TimeWed Dec 19 2018 09:43:41 GMT+0100 (Central European Standard Time)
Version13.0.7683.188
Cluster URIhttps://wabi-europe-north-b-redirect.analysis.windows.net

The issue is only in Power BI Service. I downloaded the .pbix from Power BI Service, opened it in Power BI Desktop and refreshed the data, the visual worked without any issues. 

[v-qiuyu-msft]

Hi @Anonymous,

In Power BI desktop, please check if calculated column 'Sensors'[IsInactive] and other DAX which reference this column have any error. 

If you republish the report to Power BI service, then access the report again, does the same issue occur? 

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-msft

I tried this. I get no errors in the DAX and no errors in the visual in Power BI Desktop. When I republish, I get the same error as before. 

Activity IDb0bbb1d3-c167-4faf-849c-4cda5ca52ea5
Request IDd5bcc469-ada1-cf43-69a3-504bcf75f30c
Correlation IDbfed269b-a1f6-7b55-f8f2-f789572a5169
TimeThu Dec 20 2018 08:36:42 GMT+0100 (Central European Standard Time)
Version13.0.7683.188
Cluster URIhttps://wabi-europe-north-b-redirect.analysis.windows.net

[v-qiuyu-msft]

Hi @Anonymous,

Till now, it's hard to provide further help on forum side. I would suggest you create a support ticket to let engineers look into the issue on your side. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi, 
Were you able to solve this issue? Because currently, I am experiencing the same issue. I am getting the Fiscal_Week in Table_A from Date table according to the date present in Table_A. It is working fine in PBI Desktop, but creating error when published to PBI web. Any way to solve this issue?

Formula I am using : 

Fiscal_Week = IF

(

'Table_A'[DATE_NEW] <> BLANK(),

LOOKUPVALUE(DATE_DIM[FISCAL_WEEK], DATE_DIM[CALENDAR_DATE], 'Table_A'[DATE_NEW]),

"NA"

)

The Data type is Text and looks as "WK 31". 

The error details are as follows:
Activity ID: 96b39068-18de-47d2-a482-95525d23d35e
Request ID: b9c36d0e-30e2-4f41-803f-85d3c4ab3f4f
Correlation ID: 53709eaf-7d65-17be-b76b-689c4b4198e9
Time: Fri Jan 14 2022 13:06:12 GMT+0100 (Central European Standard Time)
Service version: 13.0.17407.45
Cluster URI: https://wabi-us-east2-redirect.analysis.windows.net/

[irislarin]

Was this issues resolved ? If so how? Facing same problem

[magicbiman]

I had the same issue, but I've worked out what was causing it in my case. One of my data sources, which the calculated column was referencing, didn't have it's privacy level appropriately defined in keeping with the other sources of data. This didn't prevent the desktop version from working, but when published to Power BI  service it was preventing the data from being retrieved.

You can check this from the Home bar -> data source settings -> select your referenced data source -> edit permissions. In my case the privacy level for the referenced data source hadn't been defined. Defining it appropriately resolved the issue.