cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smileamile2
Frequent Visitor

Relationship issue, not typical fact-dim relationship

I have 3 tables with 1:* relationships and bidirectional filtering. I am seeing some weird results when I try to bring the table together in a table and I'm assuming it's because of relationship setup (this is a small part of a much bigger data model, but for this scenario I'm just including fields from 3 related tables.

 

The Shipment table is essentially a master table with two more detailed tables. I see expected results when I just include the ShipmentNum and CostAmount in my matrix, but when I add ShipBillNum from the ShipmentDetail table, the CostAmount goes blank (although it still is included in the total). Because there isn't a related record in the ShipmentDetail table, for the line to show up in my matrix I selected "Show items with no data" on ShipBillNum.  I noticed that if I select "First" instead of "Don't Summarize" on ShipBillNum, CostAmount shows up again, but I want the lines to be broken out when there are  multiple ShipBillNums.

 

Is there any way I can make this work without the ShipmentCost table being directly related to the ShipmentDetail table?

 

Edit:  When I change the CostAmount measure to "Not Summarize", the cost shows up but is obviously broken out to multiple lines when I really just want 1. Do I need to setup my tables differently?

smileamile2_0-1623432741621.png

 

1 ACCEPTED SOLUTION

This didn't work for me, I'm guessing because my relationships have bi-directional filters. I think the tables/model just need to be setup differently because the different granularities are causing issues. Thanks

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @smileamile2 ,

 

For your result, because the corresponding ShipID is not added to the visual, powerbi cannot determine the conditions under which you want to perform the calculation. I did a test and put the corresponding ShipID into the table visual to get the correct result.

v-henryk-mstf_0-1623661810225.png

v-henryk-mstf_1-1623661820895.png


Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This didn't work for me, I'm guessing because my relationships have bi-directional filters. I think the tables/model just need to be setup differently because the different granularities are causing issues. Thanks

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors