Figuring out a data model

[PoetryinMotion1]

Hi all,

Struggling with this one, although I might be overthinking it.
My data model has three tables listed below. I need to create a matrix with the following columns: 

a. Stage name

     a1. Sub-stage

b. Expected expense

c. Spent.

How can I ensure that all Stage Names are used, and when a row from the Fact Table such as the ones that have Stage 500 don't have a matching value in the Expected Expense table they will still show up in the matrix and vice versa? I'm thinking it would need to be a union of the Stage in tables 1 and 3 and duplicates removed.

Any ideas would be welcome.

1. Expected Expense:          

Project ID	Stage	Sub Stage	Expected Expense	Stage Name
123456	100	160	10000	Phase 1
123456	100	180	20000	Phase 1
123456	200	210	30000	Phase 2
123456	200	230	40000	Phase 2
123456	300	310	50000	Phase 3
123456	300	320	60000	Phase 3
123456	400	410	70000	Phase 4
123456	400	420	80000	Phase 4

2. Stage and Sub-Stage master list:

Stage	Sub Stage
100	160
100	180
200	210
200	230
300	310
300	320
400	410
400	420
500	510
SUV100	SUV110
SUV200	SUV220

3. Fact Table:

Spent	Stage	Sub Stage	Project ID
3516	100	160	123456
574	100	160	123456
4108	100	160	123456
539	100	180	123456
122	200	230	123456
1420	200	230	123456
242	200	230	123456
2652	300	310	123456
2953	300	310	123456
4015	400	410	123456
4742	400	410	123456
2535	500	510	123456
4854	500	510	123456
2656	500	510	123456

[v-yiruan-msft · Accepted solution]

Hi @PoetryinMotion1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Best Regardsd