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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Iamnvt
Continued Contributor
Continued Contributor

Userelationship - need to retain the original one in the filter pivot

hi,

 

I have a sales table:

 

Product	Order Date	Ship Date	Qty
A	1/1/2018	1/3/2018	5
B	1/2/2018	1/4/2018	5
C	1/4/2018	1/5/2018	4
D	1/5/2018	1/6/2018	4

 

and a Calendar table, the main relationship is between Order Date and Date

Ship Date, and Date are inactive relationship.

 

I wrote a measure to calculate the shipped quantity, simple:

 

=CALCULATE(sum(Sales[Qty]), USERELATIONSHIP('Date'[Date],Sales[Ship Date]))

 

 

the issue is when I drag into a pivot table, the main relationship is now modified as the inactive one.

how can I retain the main relationship in the pivot? I can't use the "order date" in Sales, because it has to be hidden.

 

expected result:

Date	Ship Date	Product	Total Ship
1/1/2018	1/3/2018	A	5
1/2/2018	1/4/2018	B	5
1/4/2018	1/5/2018	C	4
1/5/2018	1/6/2018	D	4

you can refer to the excel in this below link:

https://1drv.ms/x/s!Aps8poidQa5zkvIARmnyROQL5QAHAw

 

 

thanks

1 ACCEPTED SOLUTION

Hi @Iamnvt,

After research, the formula use the new relationship between Sales[ship date] and Date[date] when you use USERELATIONSHIP. We can't retain the original one, because there is only one active relationship between two tables, we can't use two relationship between them.

Best Regards,
Angelia

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

 

I do not understand your question.  What is the difference between the expected result and the Sales Table?  They look the same to me.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Iamnvt
Continued Contributor
Continued Contributor

that's true.

it is sort of replicating the Sales table in the pivot result.

This is just an extreme example, but I will need to apply to a more complicated situation.

 

The issue is that after I use USERELATIONSHIP, the date is that originally has the link with the Order date, becomes Ship date.

I want to keep the link of Order date in the pivot table.

Hi,

 

Would request you to please take a relevant example so that the base data and expected result can be easily differentiated.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Iamnvt
Continued Contributor
Continued Contributor

Thank you for your answer.

This example perfectly simulates the behavior issue of USERELATIONSHIP that it modifies the original relationship in the initial filter context.
Meanwhile, sometimes, we want the USERELATIONSHIP to modify only in the filter of CALCULATE in a measure. That's my question how to change that.

Hi @Iamnvt,

 
Sorry to confuse you, I post the wrong reply. The behavior of USERELATIONSHIP that it modifies the original relationship only when you create the measure [Total Ship]. The original relationship is still there. When you add the measure [Total Sales] in your pivot table, it will shows correctly, please review the following screenshot.

1.PNG


Best Regards,
Angelia

 

Iamnvt
Continued Contributor
Continued Contributor

hi @v-huizhn-msft,

 

sorry, but I couldn't understand what you mean.

 

Br, T

Hi @Iamnvt,

Please review my update reply.

Best Regards,
Angelia

Iamnvt
Continued Contributor
Continued Contributor

hi @v-huizhn-msft,

 

if you look at the date of 3/1/2018, it is not the original relationship of Order date vs Date.

 

Iamnvt
Continued Contributor
Continued Contributor

@v-huizhn-msft Could you help to check it again? Date and Order date column are different in your picture.

 

thanks

Hi @Iamnvt,

After research, the formula use the new relationship between Sales[ship date] and Date[date] when you use USERELATIONSHIP. We can't retain the original one, because there is only one active relationship between two tables, we can't use two relationship between them.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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