Forum Discussion
Many to Many Relationship
I am modeling a Many to Many Relationship. Detail below, When I try to sum by ShippingCompanyId, I get the same Amount, How would I fix this?? We have a very complicated data mart, and have many of these similar scenarios.
I have attempted changing the many to many relationships, etc
Sample Values:
create table dbo.DimCustomer
(
DimCustomerId int,
PersonName varchar(255)
)
insert into dbo.DimCustomer
values ('1','Joe'),
('2', 'Sally')
create table dbo.DimShippingCompany
(
DimShippingCompanyid int,
ShippingName varchar(255)
)
insert into dbo.DimShippingCompany
values ('1','UPS'),
('2', 'Fedex')
create table dbo.FactShipment
(
FactShipmentId int primary key,
DimCustomerId int,
DimShippingCompany int,
ShipmentQuantity int,
ShipmentTotal numeric(10,2)
)
insert into dbo.FactShipment
values (1,1,1,5,8),
(2,1,1,7,12),
(3,1,2,5,9),
(4,2,1,3,4),
(5,2,2,5,7)
create table dbo.FactOrder
(
FactOrderId int primary key identity(1,1),
DimCustomerId int,
OrderAmount numeric(10,2)
)
insert into dbo.FactOrder
values (1,50),
(1,28),
(2,41)
1 Reply
- AnonymousNot applicable
google this: Power BI multiple Fact Tables
There are a couple of videos on how to set things up when you use multiple fact tables.