Forum Discussion

JoeThomas592's avatar
JoeThomas592
Frequent Visitor
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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.