Forum Discussion
Handling different dimensional granularities in Power BI
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
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
