Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Multiple fact table and linking

I am having a hard time relating two tables with different dimensions. 

 

Below, I provide a mockup file showing ACTUALS and TARGET information. ACTUALS are shown per dateday, while TARGETS are only specified for either week day or weeken day. Both have a corresponding dimension CATEGORY. I am not able to share files on this forum yet (lost login of prior account due to switching organisation), so I also share wetransfer link with the excel file. I am very much aware someone should be hesitant to download an excel file from an unkown user so I copy paste these tables below.

 

Could someone help me find a good way modeling these tables together to show an overview of the sales per day with the target next to it?

 

I see it should be possible using the query editor to merge both fact tables into one table, but I am not very sure how this works concerning performance, as well I'd like to stick to the modeling part in the relations window to preserve understandability for other users. 

 

Thank you in advance!

 

https://wetransfer.com/downloads/36dc0fe9432775324cf03c51711f406b20181109102055/2a31c7 

 

OR

Targets:

Day_typeCategoryTarget_location_ATarget_location_BTarget_location_C
Week dayA400500525
Weekend dayA425525575
Week dayB315415440
Weekend dayB340440490

 

DateCategoryActuals_location_AActuals_location_BActuals_location_C
1-1-2018A500400400
2-1-2018A700300560
4-1-2018A610450540
5-1-2018A450500560
7-1-2018A540560500
8-1-2018A610660560
9-1-2018A560390660
11-1-2018A660450390
12-1-2018A390440450
13-1-2018A410500540
15-1-2018A450560610
16-1-2018A440610440
17-1-2018A500450500
18-1-2018A560540560
19-1-2018A520660610
21-1-2018A530390450
22-1-2018A430410500
23-1-2018A400500560
25-1-2018A500400540
26-1-2018A400560660
28-1-2018A300520390
29-1-2018A450390630
31-1-2018A500410400
1-1-2018B425325325
2-1-2018B625225485
4-1-2018B535375465
5-1-2018B375425485
7-1-2018B465485425
8-1-2018B535585485
9-1-2018B485315585
11-1-2018B585375315
12-1-2018B315365375
13-1-2018B335425465
15-1-2018B375485535
16-1-2018B365535365
17-1-2018B425375425
18-1-2018B485465485
19-1-2018B445585535
21-1-2018B455315375
22-1-2018B355335425
23-1-2018B325425485
25-1-2018B425325465
26-1-2018B325485585
28-1-2018B225445315
29-1-2018B375315555
31-1-2018B425335325
Need Help

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    I can't download the file as it is expired. It seems you may try to add columns to get the value from TARGET table as below:

    Day_type =
IF ( WEEKDAY ( 'Fact'[Date], 1 ) < 6, "Week day", "Weekend day" )
    Target_Location_A =
LOOKUPVALUE (
    Target[Target_location_A],
    Target[Day_type], 'Fact'[Day_type],
    Target[Category], 'Fact'[Category]
)

     

    Regards,

    Cherie