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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Many-to-Many Connection

  

Hey,

 

I'm trying to make a Power Pivot table that shows me a list of departments with its sales vs. goals. I'm using two DB's - MonthlyGoals and MonthlySales. Because there is many-to-many connection, I'm usind 3rd table with the list of my departments:

 

1.PNG

  

2.PNG

 

At the pivot table, I am dragging the Department table to the rows section, at the values section I am dragging Sales from MonthlySales, but when I try to dragging Goal from MonthlyGoals - the PowerPivot says there is no connection and unfortunately I cant create one.

 

How can i solve this problem?

Many thanks!

 

1 ACCEPTED SOLUTION

Please find  the file

https://www.dropbox.com/s/rvsg9nb7wjfg4y9/sample_v1.1.pbix?dl=0

 

Created a monthly table. And joined the tables on that. Please check the converted matrix, if that can help.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

It not clear in the picture. But was it dotted line before you highlight the join? If yes, it was an inactive join. Please open properties and make it active.  Also, Delete and create it once again. If it did not work then If it is possible please share a sample pbix file after removing sensitive information.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Thanks

Anonymous
Not applicable

Hey, Accidentally I highlighted the connection - it doesnt mean nothing. How can i share the file here? I alreday removed a sensitivie info.

You can share via SharePoint, dropbox etc

Anonymous
Not applicable

Here the file,

https://ufile.io/eyl98nu6

 

You can see for example that DepartmentSale1 Sold 252 units in 01/2019 and 4 units at 02/2019, and i want to show each sales and goals speratly (using pivot columns)

Please find  the file

https://www.dropbox.com/s/rvsg9nb7wjfg4y9/sample_v1.1.pbix?dl=0

 

Created a monthly table. And joined the tables on that. Please check the converted matrix, if that can help.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous
Not applicable

Many Thanks!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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