topic Re: Problem with USERELATIONSHIP in DAX Commands and Tips

Problem with USERELATIONSHIP

HarsimranPWRBI — Thu, 13 Jun 2019 11:24:12 GMT

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:.

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

Re: Problem with USERELATIONSHIP

avanderschilden — Thu, 13 Jun 2019 18:12:14 GMT

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

Re: Problem with USERELATIONSHIP

HarsimranPWRBI — Fri, 14 Jun 2019 08:00:59 GMT

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

Re: Problem with USERELATIONSHIP

avanderschilden — Fri, 14 Jun 2019 08:07:28 GMT

Great! So it is solved right?

Re: Problem with USERELATIONSHIP

HarsimranPWRBI — Fri, 14 Jun 2019 08:39:23 GMT

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.

Re: Problem with USERELATIONSHIP

avanderschilden — Fri, 14 Jun 2019 09:01:19 GMT

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

Delivered Amount 2017 =

CALCULATE([Delivered Amount],

YEAR('Date'[Date])=2018)

Re: Problem with USERELATIONSHIP

HarsimranPWRBI — Fri, 14 Jun 2019 09:14:30 GMT

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 :manhappy:

Regards,

Harry