Forum Discussion
Two Fact tables with different granularity
Hello,
I have two "fact" tables reflecting tow different subjects: the invoices and projects. I need to compare results Budget vs Expences. This is the hierarchy in each of these tables:
Table Invoices:
Client
Contract
Invoice
Table Projects:
Client
Project
Activity
I have the following dimension tables: Client, Contract, Project, Date. How could I resolve the issue of different granularity between these two fact tables in Power BI?
Many thanks!
In this secnario, if you want both tables can be sliced by all dimensions, you need to combine these two tables together. Since you have "Invoice Date" and "Client" columns in both tables, you can crossjoin these two tables into one table based on those columns.
Regards,
7 Replies
Hi beno,
Are you able to show an example of the data? What relationships have you got between your tables, can you provide a snapshot?
Thanks,
Giles
Hi GilesWalker,
The reality is a little more complex! In fact, I have tried to summarize and focus the question on this issue. However, this is a sample of data where you can see clearly this different granularity levels:
Table Invoices:
Client No. Contract No. Invoice No. Invoice Date
200080 90003 .... (Day level)
200080 90054 .... (Day level)
Table Projects:
Client No. Project Code Activity Code Invoice Date
200080 P1 .... (Day level)
200080 P2 .... (Day level)
200080 P3 .... (Day level)
200080 P4 .... (Day level)
200080 P5 .... (Day level)
200080 P6 .... (Day level)
200080 P7 .... (Day level)The relaltions between "dimension" and "fact" tables are always Many-to-One, both direction.
Thanks once again.
