Modeling with multiple date columns in fact table

[alm0001]

In the case of a fact_table for a car manufacturing process with a lot of information, in which there are at least 10 to 20 dates, including intermediate statuses, whether it has an initial payment or already has credit, and the amortized amount, what is the best way to have the model? The options:
1) Userelationship
2) Unpivot the dates

- In case 1, using userelationship would result in many inactive relationships, and the model would look terrible and not be optimal for even calculations, as I understand it.
- In case 2 (Unpivot), there is only one relationship, but the main fact table would possibly be split into several tables:
A. to only have process dates
B. for financial information
What is the best recommendation?

[v-ssriganesh]

Hello @alm0001,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hi @alm0001,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Ashish_Mathur, @EHISAdmin, @MFelix & @MohamedFowzan1  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[Ashish_Mathur]

Hi,

I would prefer method 2.

[EHISAdmin]

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. 

Active in Date Range = CALCULATE(

    COUNTROWS(Facility),

    FILTER( Facility,

        Facility[dateclosed] >= MIN('Date Table'[Date]) &&

        Facility[First Inspection]<= MAX('Date Table'[Date])  

    )

)

[MFelix]

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.

[MohamedFowzan1 · Accepted solution]

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.