Forum Discussion

orihime0's avatar
orihime0
Regular Visitor
1 year ago
Solved

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

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 BUnique CountNon Unique Count
A12
B12
C01
D00

 

Any help is much appreciated!

  • For your reference.

     

    "Model view"

     

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

     

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

     

     

4 Replies

  • For your reference.

     

    "Model view"

     

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

     

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

     

     

  • 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's avatar
      orihime0
      Regular Visitor

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.