Measure using USERELATIONSHIP works in Power BI Desktop but not in Power BI Service

[msimanek]

Hi Community,

Few days ago, one particular issue appeared in our report's measure on Power BI Service.

Measure is using inactive M:N relationship which is activated in measure using USERELATIONSHIP()

In Power BI Desktop, measure is calculated correctly (realationship is used):

But when I upload file to Power BI Service, from some reason, USERELATIONSHIP is not working:

There is another inactive relationship M:1 which is working fine.

Is there anyone else experiencing this issue? To me it seems as a PBI Service related bug. It was working fine on Service few days ago.

Thanks!

Best,

Michal

[v-yetao1-msft]

Hi @msimanek 

Have you done any updates to the data during this time? What was your initial data source? Currently, your USERELATIONSHIP still works fine in Desktop, right? Does USERELATIONSHIP work if you rename the report and upload it to Service?

Best Regards,
Community Support Team _ Ailsa Tao

[andreasnord]

I have the same problem. I tried renaming and publishing and the problem remains

[msimanek]

Hi @v-yetao1-msft,

• Datasets for affected reports (with USERELATIONSHIP) are refreshed once daily from Monday to Friday - changing/updating data if there were changes in underlying source that day.
• Initial data source is combination of Access DB and Web contents using our company API - "Access.Database(Web.Contents(API))"
• Currently, in Power BI Desktop, USERELATIONSHIP  still works.
• It doesn't work even if I rename and republish the report (also with scheduled refresh disabled)

Thanks @andreasnord  for confirming.

[msimanek]

Hi,

I invesitgate this issue further and found that issue could be "null" values on both sides of relationship.

I tried to create sample which would mimic our reports:

There are cases with "blank" values in IDs on both sides of relationship, if Blanks are selected that sum for those "blank" records in LEFT_TABLE are also added to the SUM.

Since we haven't done any changes in formulas on our side and data had those null values historicaly, I guess there could be change how relationship works in general, that means "blanks" were excluded by default and now they are included by default?

So for this sample, before the issue measure would return value 65 (excluded blanks) instead of 265 (included blanks).

Could anyone confirm or clarify how blanks are handled in those relationships by default?

I'm including sample pbix file: https://drive.google.com/file/d/1FrvYI7hLkljWT63MAZFpSgOp92w-e_s9/view?usp=drive_link .

Thanks,

Michal