Hello everyone,I am facing some issue with Power BI. I recently updated my Power Query Editor from " 'Power Query M' language and Applied Steps" to a full data transformation via a Python script implemented in one query.Since then my measures using USERELATIONSHIP related to Date do not work anymore. I checked the "Model" section and everything is as before. The screenshots below show a visual (with his settings and the formula using USERELATIONSHIP) before the update: Note: The problem do not come from the use of semi-colon. Here is the visual, with the exact same settings and formula, after the update (you can see that the "MonthYear" axis is "(Blank)") : Thank you for your response.

Anonymous , Yes. That is the problem. Create a date column and join that with date table TASK Date = [TASK Created].Date

USERELATIONSHIP do not work anymore | Microsoft Fabric Community

6 Replies

amitchandak
Super User
6 years ago
Anonymous , I doubt Task created has timestamp. Please change the data type to datetime and choose format to have time to check

Create one without time

Task Date =[Task created.Date

Join this column and use
- Anonymous
  Not applicable
  6 years ago
  Hello amitchandak ,
  
  Thanks for your response,
  
  I don't think this is the problem. As you can see on the screenshot below, the data type for "TASK Created" is datetime.
  
  I have no clue where the problem come from.
  - amitchandak
    Super User
    6 years ago
    Anonymous , Yes. That is the problem. Create a date column and join that with date table
    
    TASK Date = [TASK Created].Date
v-alq-msft
Community Support
6 years ago
Hi, Anonymous

Please make sure date type of the two columns from two tables are both the same. About USERELATIONSHIP function, please refer to the document .

USERELATIONSHIP(<columnName1>,<columnName2>)

<columnName1> usually represents the many side of the relationship to be used. This argument cannot be an expression.

<columnName2> usually represents the one side or lookup side of the relationship to be used. This argument cannot be an expression.

I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

Here are the relationships. 'StartDate'<->'Calendar' relationship is active. 'EndDate'<->'Calendar' is inactive.

You may create two measures as below.

RelationStart = CALCULATE( SUM('Table'[Value]), USERELATIONSHIP('Table'[StartDate],'Calendar'[Date]), MONTH('Calendar'[Date])>7 ) RelationEnd = CALCULATE( SUM('Table'[Value]), USERELATIONSHIP('Table'[EndDate],'Calendar'[Date]), MONTH('Calendar'[Date])>7 )

It works for 'RelationEnd' which use an inactive relationship, where <columnName1> and <columnName2> have the right order.

When i reverse the order of two parameters, 'RelationEnd' doesn't work

RelationEnd = CALCULATE( SUM('Table'[Value]), USERELATIONSHIP('Calendar'[Date],'Table'[EndDate]), MONTH('Calendar'[Date])>7 )

So please make sure <columnName1> and <columnName2> have the right order.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

USERELATIONSHIP do not work anymore.pbix52 KB
- Anonymous
  Not applicable
  6 years ago
  Hi v-alq-msft, thank you fro the answer.
  
  I did as you said and it still doesn't work. I will keep looking for a solution.
  
  Best regards,
  Samuel.