Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rajiv1237
Resolver I
Resolver I

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.

 

Now I have to create a single visualization that can show product and Sales and collection amount side by side.

 

ProductCollection and ProductSales table cannot be connected because of repeated product values.

1 ACCEPTED SOLUTION
Rajiv1237
Resolver I
Resolver I

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])))

 

ManyToMany_1.PNG

 

 

ManyToMany_2.PNG

 

 

 

View solution in original post

4 REPLIES 4
Rajiv1237
Resolver I
Resolver I

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])))

 

ManyToMany_1.PNG

 

 

ManyToMany_2.PNG

 

 

 

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.

 

 

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?Capture.PNG

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.