many to many relation only shows matching rows

[Anonymous]

hi, i have two tables with many to many relation between them on common column 'Address'. In the table visual, when I drag those two address fields from both tables, it only shows the matching rows. I want to see non matching rows as well from both tables. How can i do it?

[v-frfei-msft]

Hi @Anonymous,

Here I suggest you do not create N:N relationship between tables directly. You can creat a calculated table as a bridge table. Assume that you have two fact tables, Table 1 and Table 2. To create a calculated table using the formula.

Bridge table = UNION(VALUES(Table1[Column]),VALUES(Table2[column]))

After that, to create relationships accrodingly. If any other question, feel free to let me know please.

Regards,

Frank

[Anonymous]

@v-frfei-msft i tried creating bridge table. But to get all non matching rows from both tables i had to use merge query with full outer join,,,if i just use bridge table and set up N:1:N ( table1:bridge:table2) b/w them i still dont get all rows, it just gives me matching rows.  The merge with full outer join gives me the result, without creating any relationships and without create any bridge table. So what is the use of bridge table and setting up relationships here? 

[v-frfei-msft]

Hi @Anonymous,

Here I made one sample for your refernce. To create a calculated table using the formula and create relationships between tables.

BT = DISTINCT(UNION(VALUES(Table1[cate]),VALUES(Table2[cate])))

Then we can create a table visual like this, here the data are summarized. If you don't want the summarized data, then you can only merge the tables as you said.

Regards,

Frank