Managing relationship many-to-one - show blanks for 'one-table' if no value is found in 'many-table'

[Anonymous]

Hello community,

My apologies in advance if this topic is already handled elsewhere.

I have two tables.

1. The order table contains order lines (one line per order) with order information

2. the part table contains part information for orders, with multiple lines possible per order.

Not every order in the order table has part information (see O3), and the part table has order id's that are not in the the order table (see O5)

 

I can create a relationship for the tables with cross filter in single and both directions.

However, when trying to combine data from both tables in a visual, it appears that data for which no part data can be found is left out.

I get this output

While i was hoping to get this output where order O3 is also included, but with blank part information

order from order	order from parts	part
O1	O1	jeans
O1	O1	shirt
O1	O1	sweater
O2	O2	jeans
O2	O2	shirt
O3
O4	O4	jeans
	O5	sweater

Any tips on how to solve this?

[Anonymous]

Thanks for the help everyone. I recreated the excisting table and using the 'show items with no data' option. This worked this time

[Smauro]

Hello @Anonymous,

The issue is on the Many-to-One relationship, and it's actually not an issue. Let me explain.

The many side of the Many-to-One relationship, 'part' table is seen as the point of reference in the table visual you're trying to create. In 'part' there is no O3, and hence the algorimth does not even look for it in order to display it:

It first looks at the values of 'part table'[order] and then it connects them to any existing in 'order table'[order]. After finding the matches from 'part' to 'order', because the relationship is bidirectional it says that "from those you found, filter accordingly both ways.
Your confusion is on that a bidirectional Many-to-One is not a One-to-Many and a Many-to-One combined.

The easiest way I could think of solving your issue is to do a Full Outer merge in the Query editor between the two tables using the [order] columns.

[Gazzer]

I believe @Smauro is correct.

You need to merge the tables in the editor, where you will be able to do a full outer join to get what you want.

[Anonymous · Accepted solution]

Hi, 

If you click on your table, then on the tool panel on the right when you have selected the items to appear in the table - click on the small arrow next to 'Order from Order' and select 'Show Items with No Data' (usually works for me) 

Thanks

[Anonymous]

Thank you JamesBI2. Unfortunatly this does not work