Two Fact tables with different granularity

[beno]

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!

[GilesWalker]

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

[beno]

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.

[v-sihou-msft · Accepted solution]

@beno

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, 

[beno]

Thanks. combining the two fact tables in one, reduced modeling efforts.

[Xinhui04]

I am facing similar issue and having 2 fact tables with different granularity. Can you share how to cross join and how would the table looks like after merging?

[CheenuSing]

@beno

Please share  the type of output are you expecting ?