Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

USERELATIONSHIP do not work anymore

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.

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

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

     

    TASK Date = [TASK Created].Date

     

     

6 Replies

  • amitchandak's avatar
    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's avatar
      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's avatar
        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's avatar
    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.

     

    • Anonymous's avatar
      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.