Forum Discussion
Modeling with multiple date columns in fact table
Hi alm0001
Regarding the scenario you mentioned,
I would definitely recommend going with the second option of unpivoting the dates.Modeling with so many inactive relationships and relying heavily on USERELATIONSHIP would not only make the model look cluttered but also result in complex DAX calculations. Moreover, it would be difficult to maintain as additional requirements arise.
While unpivoting might add some extra steps in Power Query, it will make the model significantly easier to maintain. If feasible, you could even adopt a star schema using this approach. Overall, the performance and readability of the model would be much better compared to managing numerous inactive relationships.
Hi alm0001 ,
In my opinion, you should go for option 1 and if you have several calculations that need to be recurrent based on the relationships what you can do is to create calculation groups that will make the relationships that you need to be called for each of the measures that way you don't need to do 10 to 20 measure of each of the calculations.
Again in my opinion the unpivot of the dates can bring you additional problems, on simple measures like sum and counts specially when you refer at analysis of information that is not date related so you would need to make a summarization or a filter on your table in order to get the correct result.
Having multiple fact tables is also not a problem if you have the correct relationships in place and the correct calculations I believe this mainly depends on the final calculations and visualizations but also on performance that you may or not impact based on each of the approachs.