Forum Discussion

ZealandZu's avatar
ZealandZu
Frequent Visitor
3 years ago
Solved

Connecting tables with different granularity and creating DAX measures that uses figures from both

So I need help regarding tables with different granularity, the join between them with the dax measure subracting between them.

 

In Table 1, I have a fact table of the tasks employees have booked time too on a particalar day. 

 

Year/MonthDateTaskEmployeeHours Booked
2023/0615/06/2312345Julia Roberts4.7
2023/0615/06/2312345Brad Pitt5.2
2023/0615/06/2367894Brad Pitt1.2
2023/0616/06/2312345Julia Roberts3.3
2023/0616/06/2355555Brad Pitt8
..............

 

Next in Table 2 i have a DIM table with which tasks are part of which project along with status etc

 

TaskProjectStatus
12345ToyotaOpen
67894ToyotaOpen
55555HondaOpen
33333SkodaClosed
...........

 

These two tables are connected by the task number

 

I also have a DIM Calendar connected to the fact Table 1 via Date

 

Now this all rolls up fine. I can put Toyota in a matrix along with the Hours booked column and i get the right totals.

 

However I also have Table 3 that has forecasted hours by Project

 

ProjectYear/MonthForecasted Hours
Toyota2023/0550
Toyota2023/0662
Toyota2023/0720
Honda2023/0534
Honda2023/06100
.........

 

Now how do i somehow connect these table together so that eventually get a table like this:

 

Project

Year/Month

Total Hours

Forecasted Hours

Toyota

2023/06

14.4

62

Toyota

2023/07

0

20

Honda

2023/06

3.3

100

...

....

....

....

 

Which i can then use to create measures/calc columns where i can subtract total hours from the forecast hours, create condional formatting measures etc etc.

 

Many thanks

  • You control both fact tables by your dimension tables.  In your visual the columns (Project and Year/Month)  need to come from your dimension tables and the hours data from the fact tables.

     

     

    see attached

     

2 Replies

  • You control both fact tables by your dimension tables.  In your visual the columns (Project and Year/Month)  need to come from your dimension tables and the hours data from the fact tables.

     

     

    see attached