Unable to establish relationship for two fact tables with multiple many-to-many dimensions in Excel-CodePudding

I have 3 tables (FACT1, FACT2, DIM1, DIM2)

FACT1:

Code Month Value
058 1 500
059 1 600
061 1 700
058 2 1000
059 2 1000
061 2 1000

FACT2:

Service Month Status Value
058-buy 1 OK 700
059-purchase 1 Missing 800
061-trade 1 OK 900
058-buy 2 OK 300
059-purchase 2 Missing 400
061-trade 2 OK 500

DIM1:

Code Service
058 058-buy
059 059-purchase
061 061-trade

DIM2:

Month Name
1 January
2 February
3 March

I have all 4 tables loaded into Data Model in Excel and created a new measure in FACT1 Value Total:=sum([Value]) sumx(FILTER('FACT2','FACT2'[Status]="OK"),'FACT2'[VALUE])

I have also created relationship between tables: FACT1(Code) to DIM1(Code) FACT2(Service) to DIM1(Service) FACT1(Month) to DIM2(Month) FACT2(Month) to DIM2(Month)

However, when I insert a new pivot using the data model by having Code from DIM1 on "ROW" Month from FACT1 on "COLUMN" New Measure Value Total on "Values"

I get something like this:

Code 1 2 Grand Total
058 1500 2000 2500
059 600 1000 1600
061 2100 2400 3100
Grand Total 4200 5400 7200

Somehow it will get sliced by Month properly, but if you pay attention to the last column of the pivot table Grand Total, they are correct! 2500 is indeed 500 700 1000 300, 1600 is indeed 600 1000, 3100 is indeed 700 900 1000 500 The final grand total for the above 3 is correct at 7200 too.

Now the question is why the middle part of the pivot tables acts oddly?

CodePudding user response：

For your Pivot Table, use Month from the DIM2 table, not the FACT1 table. With your current set-up, FACT1 cannot filter the FACT2 table, though DIM2 can.