Handling different dimensional granularities in Power BI

[RobWalker]

Hi all,

A general modelling problem and would just like to understand how this is commonly approached in Power BI.

As an example, I have a sales fact table and to keep it simple it just has a sales amount and a date.  The date key joins to a calendar dimension that has the granularity of day.  All good.

However, if i have another fact table that is the level of month key and i join this to the same calendar dimension i presume i will get double counting i.e. month key multipled the days in a month?

Do i just need to create another calendar dimension that is aggregated to month instead of day and join to that or is there another technique that should be applied instead?  Without naming them, another vendor has a solution where the dimension table will group itself up to the appropriate granularity first and then do the join but i can't see an equivalent in Power BI or am i missing something?

Any advice on how you would approach this?

Many thanks

[Alex_Serebr]

In the second fact table (month level) just create calculated column First (or Last) DayOfMonth and use this column for relationship with your calendar table. It would treat all the events in your second fact table as happened in the same (first or last, depending of your business scenario) day of month

[RobWalker]

Thanks Alex, thats a great solution.  So effectively ignore the month key concept and just focus on first day or last day of month as the month key proxy.

It does highlight a flaw in my rather simple example though.  If i change the scenario slightly:

I have a fact table at the granularity of product id and i link this to the product dimension at the same level of granularity (product id) that works again like calendar date would.  But if i have a fact table at the level of product parent category and i link it product dimension, how should that be handled?  Unlike date i don't have first or last function to use in that scenario.

Many thanks

Rob