Many-to-Many Connection

[Anonymous]

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:

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!

[amitchandak]

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]

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

[amitchandak]

You can share via SharePoint, dropbox etc

[Anonymous]

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)

[amitchandak · 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

[Anonymous]

Many Thanks!