cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Pivot - Several databases - Many to Many

Good Afternoon, 

 

I'm new in PowerBI,  thank you in advance for your help. I'm trying to connect 2 databases in one PIVOT but the result is not correct. I would like to show in a pivot the number of complaints and the sales. 

 

Database 1 is : 

Year
MonthWeekCustomerProductNumber of Complaint 
2018512AChips1
2018413BChips 2 
2018512AChips11

 

Database 2

 

Year
MonthWeekCustomerProductSales 
2018512AChips1100000
2018413BChips 2 500000
2018512AChips 185000

 

DESIRED OUTPUT

 

 

Year
MonthWeekCustomerProductSum Sales Sum  complaint
2018512AChips11850002
2018413BChips 2 600000055

 

What I Did : 

  • I connected both databases via Many to Many for customer 
  • Create a Pivot
  • Select "customer" of the complaint for the row of the Pivot 
  • Select sales and complaint for the values

 

What I have : 

 

Year
MonthWeekCustomerProductSum Sales Sum  complaint
2018512AChips16185000 2
2018413BChips 2 618500055

 

Sum of sales is incorrect because the connection between databases doesn't work properly. Could you please help me ? 

 

Many thanks, 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Pivot - Several databases - Many to Many

Hi,

 

In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them. 

 

When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)

 

How to create this? 
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers

Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.

View solution in original post

5 REPLIES 5
Highlighted
Resolver II
Resolver II

Re: Pivot - Several databases - Many to Many

Hi @jessicat  ,

I suggest to concatenate Date, Customer and Product columns in both databases. Then, link both databases via the concatenated columns. 

That should do it. 

Let me know if anything is unclear.

Cheers,
Robin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Pivot - Several databases - Many to Many

Hello @RobinDeFal , 

 

Thank you for your answer. I didn want to concatenate theses columns because I need to use them separately. Finaly I renamed the columns with exactly the same name then it works but I have the same problem with another column.

 

Indeed, if I want to see the complaints and sales by week, I have the same problem : 

 

DESIRED OUTPUT

 

WeekComplaints Sales  
1218789456
135546585
142545821
152385468
168587458
1722088754

 

 

What I have : 

 

WeekComplaints Sales  
12376789456
13376546585
1437645821
1537685468
1637687458
1737688754

 

When I do "see records", I see that only one of the database is properly filtered as you can see below : 

 

Power Bi screeshot .png

 

Many thanks for your help 🙂 

 

 

 

Highlighted
Super User I
Super User I

Re: Pivot - Several databases - Many to Many

Hi,

 

In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them. 

 

When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)

 

How to create this? 
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers

Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.

View solution in original post

Highlighted
Super User III
Super User III

Re: Pivot - Several databases - Many to Many

I don't think @RobinDeFal was suggesting you concatenate the columns, but was suggesting you concatenate the tables.  It sounds like you accomplished this by changing the column names to match.  

 

If I had to guess, I would think that your Complaints and Sales tables are still not related to each other.  A normalized relational data model would be a huge benefit for you.  Your time is probably best spent here. Could you share a screenshot of your table relationships, or even better a sample .pbix file so we can see how your data is set up?  I understand if you have a need to anonymize your data, but a dozen or two rows of data of random values set up in the same format would be exceptional.

Highlighted
Frequent Visitor

Re: Pivot - Several databases - Many to Many

Hello @RobbeVL

 

You was right, stay far from many to many. Now it works perfctly. 

 

Many thanks all of you, 

 

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors