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.
It may be possible for you to to use a date table with no relationship to other tables and incorporate the date table as your filter in the DAX measures. We have to count the numner of business that are open within a date range using both the first inspection date and closure date, so use a date slicer on the report and the following DAX to count just the places that are active.