Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply

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 Man Surprised Man Frustrated.

 

user relationship w.r.t  Deliver Date CY not workinguser 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
)
)

 

1 ACCEPTED SOLUTION
avanderschilden
Resolver I
Resolver I

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

View solution in original post

6 REPLIES 6
avanderschilden
Resolver I
Resolver I

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

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

 

UserRelationShipIssue_06142019.png

Great! So it is solved right?

Yeah it worked, and last error was due to wrong column used for relationship.

 

The only thing is ,its only showing for CY2007 and not repeating for all CY.

 

UserRelationShip_06142019.png

This will show the delivered amount in 2017 on all year lines;

 

Delivered Amount 2017 =
CALCULATE([Delivered Amount],
YEAR('Date'[Date])=2018)

yeah @avanderschilden  changing your first expression from 

CALCULATE(
[SalesAmount],// its a measure with expression SUMX(Sales,Sales[Quantity]*Sales[Unit Price])
USERELATIONSHIP('Date'[DateKey],Sales[DeliveryDateKey]),
YEAR('Date'[Date])=2007 // Changed from YEAR(Sales[DeliveryDate])=2007 allowed 2007 sales w.r.t to delivered date on all CY
)

Thanks for your guidance, much appreciated Man Happy

 

Regards,

Harry

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.