How to connect fact tables that have different granularity?

[Olga_S]

Hi

I have several fact tables that have different granularities (date-year, customer-customer group, product-product group). 

How to connect these tables in data model that values defined on higher level (for ex. product group) would be repeated on lower levels (product)? 

I read that i can merge tables in Power Query, but due to the table size it takes ages to merge talbes. In addition merge does not always work (known bug with our server). Connection in data model does not wokt due to M:M relationship).

 

Thanks! 

[v-zhenbw-msft · Accepted solution]

Hi @Olga_S ,

We can create six columns to meet your requirement.

1. Create the year column in Sales table.

Year = YEAR(Sales[Date])

2. Create two columns to get the customer group and product group in Sales.

Product Group = 
CALCULATE(MAX('Product'[Product group]),FILTER('Product','Product'[Product]=Sales[Product]))

Customer Group = 
CALCULATE(MAX(Customers[Customer Group]),FILTER(Customers,Customers[Customer]=Sales[Customer]))

3. At last we can create three columns to get the result.

Product% = 
CALCULATE(SUM('Product discount'[%]),FILTER('Product discount','Product discount'[Year]=Sales[Year]&&'Product discount'[Product group]=Sales[Product Group]))

Customer% = 
CALCULATE(SUM('Customer discount'[%]),FILTER('Customer discount','Customer discount'[Year]=Sales[Year]&&'Customer discount'[Customer Group]=Sales[Customer Group]))

Total = [Product%]+[Customer%]

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@Olga_S . year you can convert to date like .

date = date([year],1,1)

for the customer discount and product discount, you do not join with customer dim or product dim. Create a filter in measure