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
smileamile2
Resolver I
Resolver I

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

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.