Modelling Many to Many Relationship and Counting Unique and Non-Unique Relationships

[orihime0]

I have 2 fact tables that consist of unique values that are related to one another through an additional item-relationship:
Fact 1

Fact 2

Item Relation Table - As seen here, not every item in Fact 1 has a relationship to an item in Fact 2, and vice-versa

Currently for visualization purposes, I have the following model. A visual table containing Items A needs to be able to filter another table with Items B down to the items related on selected Item A. (Ex. "1" is selected in one table, and the other table will filter down to A, B, and C)

I have 2 questions regarding this model.

1) Obviously setting up the model like this is going to cause severe performance issues. Is there an alternate way to model this without having to combine Fact 1 and Fact 2 and grow the table exponentially?

2) I need to create DAX measures to indicate for every Item B, the count of times it is the only related item to Items A, and the count when it is not the only item related to Item A. Example below of what the table should look like. Also, preferably, when selecting an Item B, another table will filter down to the related Items A.

Item B	Unique Count	Non Unique Count
A	1	2
B	1	2
C	0	1
D	0	0

Any help is much appreciated!

[v-tangjie-msft]

Hi @orihime0 ,

Whether the advice given by mickey64  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

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

[mickey64 · Accepted solution]

For your reference.

"Model view"

Selection "B" of "ItemB" on "Report view"

Selection "2" of Item A on "Report view"

[danextian]

I'm curious why Item A has a completely different set of values from Item B. How do you know which Item A value is related to that from Item B?

[orihime0]

As posted above, they are related to eachother through the Item Relation table: