On demand refresh issue

[cezart]

Hi,

Since new year, I’m experiencing problems with “On demand” refresh on PBI Web Service. Issue occurs in old Datasets and also in new ones, just published to Premium Workspace.

Sample data model contains 2 tables: (dimension) Calendar and (fact) Main Table. Source for those tables are .csv files.

Tables are connected with 1:* relationship between dates column.

Dates are stored in DD.MM.YYYY format.

On demand refresh performed in PBI Desktop works just fine, there’s no issue with blanks or duplicates.

Data source error:	Column '<pii>CALENDAR_DATE</pii>' in Table '<pii>dimCalendar</pii>' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. Table: dimCalendar.
Cluster URI:	WABI-NORTH-EUROPE-H-PRIMARY-redirect.analysis.windows.net
Activity ID:	cb40719f-ce23-4d24-8b31-18e6be2a364a
Request ID:	d82aae08-4301-3fef-6e30-d6f1f76e74b4
Time:	2022-01-13 10:24:44Z

Personal Gateway seems to be working fine, and all data sources are mapped in correct way:

Fixing refresh issue can be as simple as changing source of Calendar table. Setting date format to MM.DD.YYYY, and then using locale settings in PowerQuery to my "proper" settings (dd.mm.yyyy) seems to help, but publishing it to Service and refreshing it, reverts the changes, and all dates are set back to mm.dd.yyyy...

PBI Desktop Version: 2.100.785.0 64-bit (December 2021)
On-premise Gateway Version: 3000.110.3 (December 2021)

[ibarrau]

Hi. This is kind of weird. I have seen this error before but for text keys, not numbers formats.

I would suggest to use an integer key between them (mark calendar as date table to make time intelligence work). The integer as "YYYYMMDD" never fails. I usually use just "date" type with regular format and never had issues, but this sounds like it's having an issue with the format and region in service.

I hope that helps,

P/D You can also try remove duplicates (power query step) in the key column in date table just to check if the engine is capturing that before vertipaq (both engines work different)

[cezart]

Thank you for your reply ibarrau. 
I tried your method, and created source calendar with dates in integer format 'YYYYMMDD'. In PQ I'm setting column format to 'Date' (standard DD/MM/YYYY, just like OS regional settings), and loading both tables to data model. Then I'm creating relationship and publish file to Workspace, and everything works fine. After refresh 'on demand', all dates in matrix are formatted to MM/DD/YYYY, messing all the quantitative values.

Maybe exporting fact tables with date as integer would help, but it would be too tedious, cause I've plenty of sources with date columns formatted as DD.MM.YYYY

First screenshot is after publishing, and second is right after refreshing dataset.

Changing language in PBI Service from English to native doesn't work, same with unmarking all "Relationships" options in file "Data load" category.
Im honestly out of ideas, why it happend at the beginning of new year.
Nothing changed, wasn't installed or updated, whole thing just stopped working out of nowhere...

[v-easonf-msft]

Hi, @cezart 

This is so weird. On the forum side, it's hard to provide any more support about this issue.

I would suggest you create a support ticket in Power BI Support to get further help. MSFT engineers may find more information in logs.

Best Regards,
Community Support Team _ Eason