PowerPivot with 2 Tables Relating Issue! Please Help!

[traviscook21]

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!​

 

[Matt Allington]

Welcome to power pivot. Have a read of my article about joining multiple tables in your data model. What you need is to create a lookup table that has a set of unique values and then join both of your current data tables to that one common lookup table. There is a lot to learn about how power pivot works and I recommend that you take a look at my book. This will help you get it right the first time and help you go a lot faster on your journey.

Blog article Multiple Data Tables in Power Pivot - Excelerator BI
Book. Learn to Write DAX - Excelerator BI

 

[traviscook21]

Welcome to power pivot. Have a read of my article about joining multiple tables in your data model. What you need is to create a lookup table that has a set of unique values and then join both of your current data tables to that one common lookup table. There is a lot to learn about how power pivot works and I recommend that you take a look at my book. This will help you get it right the first time and help you go a lot faster on your journey.

Blog article Multiple Data Tables in Power Pivot - Excelerator BI
Book. Learn to Write DAX - Excelerator BI

Thanks for this. I've been looking for a good book and some training material on Power Pivot and Dax formulas.