Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

msimanek

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

Submitted by on ‎09-20-2023 05:34 AM

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()

 

msimanek_0-1695212768133.png

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

msimanek_1-1695212858853.png

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

msimanek_2-1695212926895.png

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

Status: Needs Info
See more ideas labeled with:
4 Comments (4 New)
Comments
Anonymous
Not applicable
‎09-20-2023 07:25 PM

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
Frequent Visitor
‎09-22-2023 04:06 AM

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

msimanek
Regular Visitor
‎09-24-2023 11:36 PM

Hi @Anonymous,

 

  • 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
Regular Visitor
‎10-23-2023 06:26 AM

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:

msimanek_0-1698067879322.png

 

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

Idea Statuses