USERELATIONSHIP do not work anymore

[Anonymous]

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.

[v-alq-msft]

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.

[Anonymous]

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.

[amitchandak]

@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]

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 · Accepted solution]

@Anonymous , Yes. That is the problem. Create a date column and join that with date table

TASK Date = [TASK Created].Date

[Anonymous]

Hello @amitchandak,

You got it right ! I duplicated the original column ( [TASK Created] ) then changed the data type to "date" in the new column. 

Creating a new column with this formula "= Datetime.Date([TASK Created])" work as well.

Thanks you !