Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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.
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.
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.
Best Regards,
Angelia
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.
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |