Pivot creating nonexistent lines

[msciblo]

I have three tables; Line Items, Order Lines and Order Payment Lines. I would like to create a pivot using Payment Type as a slicer [I filtered to remove Reships] and the pivot contains Order Number, Product Description, Last Name, First Name and State as Row Labels with Line Total and Shipping Total as Values. I created relationships between Line Items and Order Lines on Order Number and Line Items and Order Payment Lines on Order ID.

Here are the tables:

Line Items:

OrderNumber	Ship Date	Product Pin	Product Description	Units Sold	Line Total	Order ID
1	1-Jan	1	Apples	1	4	100
2	2-Jan	2	Bananas	1	3	200
2	2-Jan	2	Apples	1	4	200
2	2-Jan	3	Cherries	1	2	200
3	1-Jan	3	Cherries	1	2	300
4	3-Jan	1	Apples	1	4	400
5	5-Jan	4	Dates	1	5	500
6	8-Jan	3	Cherries	1	2	600
7	1-Jan	1	Apples	1	4	700
7	1-Jan	2	Bananas	1	3	700
7	1-Jan	3	Cherries	1	2	700
8	1-Jan	3	Cherries	1	2	800
8	1-Jan	4	Dates	1	5	800

<tbody>
</tbody>

Order Lines:

Last Name	First Name	State	Order ID	OrderNumber	Shipping Total
Smith	Emmitt	PA	100	1	5
Jones	Julio	NJ	200	2	8
Johnson	Chad	NY	300	3	6
Andrews	Julie	DE	400	4	9
Jackson	Andrew	PA	500	5	10
Boyd	Brandon	WI	600	6	5
Prime	Optimus	PA	700	7	4
Jackson	Michael	DE	800	8	2

<tbody>
</tbody>

Order Payment Lines:

Order Number	Payment Method	Order ID
1	Credit Card	100
2	Credit Card	200
3	Credit Card	300
4	Check	400
5	Reship	500
6	Reship	600
7	Reship	700
8	Credit Card	800

<tbody>
</tbody>

The pivot below is what my attempts have yielded, however Order Number 1 only has Line Item but the pivot shows lines for all Product Descriptions. Order Numbers 5, 6 and 7 are all reships so I would like them filtered out entirely.


Payment Method slicer [filtered to allow only Check and Credit Card]

					Values
OrderNumber	Product Description	Last Name	First Name	State	Sum of Line Total	Sum of Shipping Total
1	Apples	Smith	Emmitt	PA	4	5
1	Bananas	Smith	Emmitt	PA		5
1	Cherries	Smith	Emmitt	PA		5
1	Dates	Smith	Emmitt	PA		5
2	Apples	Jones	Julio	NJ	4	8
2	Bananas	Jones	Julio	NJ	3	8
2	Cherries	Jones	Julio	NJ	2	8
2	Dates	Jones	Julio	NJ		8
3	Apples	Johnson	Chad	NY		6
3	Bananas	Johnson	Chad	NY		6
3	Cherries	Johnson	Chad	NY	2	6
3	Dates	Johnson	Chad	NY		6
4	Apples	Andrews	Julie	DE	4	9
4	Bananas	Andrews	Julie	DE		9
4	Cherries	Andrews	Julie	DE		9
4	Dates	Andrews	Julie	DE		9
5	Apples	Jackson	Andrew	PA		10
5	Bananas	Jackson	Andrew	PA		10
5	Cherries	Jackson	Andrew	PA		10
5	Dates	Jackson	Andrew	PA		10
6	Apples	Boyd	Brandon	WI		5
6	Bananas	Boyd	Brandon	WI		5
6	Cherries	Boyd	Brandon	WI		5
6	Dates	Boyd	Brandon	WI		5
7	Apples	Prime	Optimus	PA		4
7	Bananas	Prime	Optimus	PA		4
7	Cherries	Prime	Optimus	PA		4
7	Dates	Prime	Optimus	PA		4
8	Apples	Jackson	Michael	DE		2
8	Bananas	Jackson	Michael	DE		2
8	Cherries	Jackson	Michael	DE	2	2
8	Dates	Jackson	Michael	DE	5	2
Grand Total					26	49

<tbody>
</tbody>

 

[Vidar]

Reply deleted...

 

[Laurent C]

[Sum of Shipping Total] is a measure that comes from a lookup table. In that case, the data will not get filtered.

I am not sure about the correct explanation, but I guess this will be something along "for every Line Item, there exists, by definition, a related Shipping Total".

In order for the filtering to work, you will have to use a measure that is based on your fact table (Line Items).

Something like this:
SUMX(LineItems, RELATED(Orders[Shipping Total]))