Forum Discussion

Rajiv1237's avatar
Rajiv1237
Icon for Resolver I rankResolver I
8 years ago
Solved

Many to Many Relationship between tables and show data in single Visualization - Power BI

Scenerio: I have two tables Table 1: ProductionCollection - It has product and collection amount. Product can repeat Table 2: ProductSales - It has product and sales amount. Product can repeat.  ...
Architecture
power bi
  • Rajiv1237's avatar
    Rajiv1237
    8 years ago

    We cannot connect Table1: ProductSales and Table2: ProductCollection directly because of many to many relationship. 

    Created a bridge table Table3: Product - that has distinct value of product from both tables.

     

    Table3: Product table creation

    Product = DISTINCT(UNION(DISTINCT(ProductCollection[ProductName]), DISTINCT(ProductSales[ProductName])))

    Product = DISTINCT(UNION(DISTINCT(ProductCollection[ProductName]), DISTINCT(ProductSales[ProductName])))

     

     

     

     

     

     

Rajiv1237's avatar
Rajiv1237
Icon for Resolver I rankResolver I
8 years ago

We cannot connect Table1: ProductSales and Table2: ProductCollection directly because of many to many relationship. 

Created a bridge table Table3: Product - that has distinct value of product from both tables.

 

Table3: Product table creation

Product = DISTINCT(UNION(DISTINCT(ProductCollection[ProductName]), DISTINCT(ProductSales[ProductName])))

Product = DISTINCT(UNION(DISTINCT(ProductCollection[ProductName]), DISTINCT(ProductSales[ProductName])))

 

 

 

 

 

 

  • steph_io's avatar
    steph_io
    Icon for Advocate II rankAdvocate II
    8 years ago

    Is there a difference between doing this in M in the Query Editor vs within DAX in the Modeling of the report?

  • jatneerjat's avatar
    jatneerjat
    Icon for Helper V rankHelper V
    8 years ago

    Hi Rajiv1237,

     

    What if we have one more column ProductType and it also has duplicate vlues and we want this column to filter both the tables and also the table product created by us.then how can we create a relationship.

     

     

    • Domantas's avatar
      Domantas
      Icon for Advocate I rankAdvocate I
      8 years ago

      I have a similar issue, just trying to make a bridge table out of 3 different tables. For some reason my outer DISTINCT doesn't seem to be able to remove duplicates of blank row:
      List-Skill groups = DISTINCT(UNION(
          VALUES(AXtimesheets[Skill Group (Category)]),
          VALUES('FTE data lookup only'[Skill group]),
          VALUES('Consolidated Allocations'[Skill group])))

       

      So I get two blank values among all other normal names of skill groups.

      Then another thing is that for the bridge table there shouldn't be no blanks at all. But to start with if I could find a way to keep only one blank, would be a good start. Any ideas?