Forum Discussion

HarsimranPWRBI's avatar
HarsimranPWRBI
Helper I
7 years ago
Solved

Problem with USERELATIONSHIP

I'm trying to create a measure to get Sales Amount w.r.t Delivered Date where CalenderYear is 2007 but having trouble with same.

 

For Sales Amount w.r.t Delivered Date works well  and show amount for Delivered Date CY 2007 with UserRelationship, but when i try to filter only for 2007 its show blank rows :mansurprised: :manfrustrated:.

 

user relationship w.r.t Deliver Date CY not working

 

I'm using ContosoDw for reference and below is code of two measure.

 

Sales w.r.t Delivered Date

Delivered Amount = 
CALCULATE (
[SalesAmount],
USERELATIONSHIP ( Sales[DeliveryDateKey], 'Date'[DateKey] )
)

Sales w.r.t to DeliveredDate for Year 2007

Delivered Amount in 2007 = 
CALCULATE (
[Sales Amount],
FILTER (
CALCULATETABLE (
Sales,
USERELATIONSHIP( Sales[DeliveryDateKey], 'Date'[DateKey] )
),
RELATED ( 'Date'[Calendar Year Number] ) = 2007
)
)

 

Help
  • avanderschilden's avatar
    avanderschilden
    7 years ago

    Hello,

     

    Try this (with the correct table and column names from your model) ;

    Delivered Amount 2007 =
    CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP('Date'[Date],Sales[DeliveryDate]),
    YEAR(Sales[DeliveryDate])=2007
    )
     
    Let me know!
     
    Regards,
     
    Adrian

6 Replies

  • avanderschilden's avatar
    avanderschilden
    Resolver I
    7 years ago

    Hello,

     

    Try this (with the correct table and column names from your model) ;

    Delivered Amount 2007 =
    CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP('Date'[Date],Sales[DeliveryDate]),
    YEAR(Sales[DeliveryDate])=2007
    )
     
    Let me know!
     
    Regards,
     
    Adrian
    • HarsimranPWRBI's avatar
      HarsimranPWRBI
      Helper I
      7 years ago

      Hi Adrian,

       

      Its showing below error (USERRELATIONSHIP function can only use the two columns references participating in relationship), while using your concept. I've replaced SUM(Sales[Amount]) with measure already build.

      Which is using an  below code as an expression 

      SUMX(Sales,Sales[Quantity]*Sales[Unit Price])