Power Pivot With 2 tables Relating Issue

Thanks for any help in advance!
I have two table in my data model (1. ProductionAnalysis 2.ARTransaction)
I need to create a pivot table pulling the accounting date from the ARTransaction table and Facility Name, Case Count, and Total Billed from the ProductionAnalysis table.

My issue is I keep getting the repeating values for each facility for each date for example it looks like this


Facility A Case Count Total Billed
7/1/16 51 200
8/1/16 34 175
Facility B
7/1/16 51 200
8/1/16 34 175
Facility C
7/1/16 51 200
8/1/16 34 175

It's as if it's not recognizing the facilities. It's just giving me the total numbers of all the facilities combined in each date.

Here are the list of columns from each tables.
ARTransaction:
ProvOrgID
AccountID
ServiceLineID
TransactionCodeID
OnAccountID
DistributionItemID
AdjustmentID
RefundItemID
Amount
AccountingDt
EntryDt
EntryBy
ID
PaymentRank


Production Analysis:
IncidentID
ServiceLineID
CPTCode
TypeOfServiceID
ASACode
PGID
PGLabel
PGValue
ProviderID
Provider
IsAnesthesiaCaption
IsAnesthesia
BillingType
SGID
SGLabel
SGValue
Quantity
TotalBilled
TotalExpected
WorkRBRVS
PracticeRBRVS
MalpracticeRBRVS
TotalRBRVS
ProcedureMinutes
BaseUnits
TimeUnits
PSU
TotalUnits
CaseCount
ActionType
ProvOrgID
ProvOrgName
ProfTypeID
ProfType
RefProvID
RefProv
IncRptClassID
IncRptClass
InsPlanID
InsPlan
InsPayerID
InsPayer
FacilityID
Facility


I can't use ServiceLineID as a relatable column because there are duplicate values of ServiceLineID in the ARTransaction table.


I know this is kinda of high level stuff, at least it is to me haha, so any help is greatly appreciated.

Thanks!

Originally Posted by xlnitwit

Hi,

What relationships have you set up between the two tables?

I believe that's the part I'm struggling with. I thought the best way was to use ServiceLineID for each table but in that column there are multiple values (serviceline ID #'s) so it wouldn't let me.

Thanks,

Originally Posted by xlnitwit

You cannot set up a many to many relationship join. What is the relationship that determines which records from one table belong to a single record in the other table?

If you cannot define that, it would seem you require an additional table.

That makes sense. I think I may have to pull in the ServiceLine table as well. I would then use that as my lookup table to link the other two tables based on the ServiceLineID column?

Thanks,

Originally Posted by xlnitwit

I can't say for sure- I don't know your data model.

I'm pretty sure this will work. It makes more sense now. Thanks for clearing that up. I'm going to need to pull in more tables because I used a sql statement to pull in my data in Power Query that is using several other tables to give me my ProductionAnalysis table.