Filter table using date filter, then use results to filter dimensions - Create FACT?

[WBscooby]

Hi

I posted some time ago and was given a solution/ measure that resolved my issue at the time. Link attached.

Solved: Filter table using date slicer, then filter dates ... - Microsoft Fabric Community

I have a date calendar, Customer Table, intervention table and attendance table. All of these tables are provided to me in a completed format.

A customer can have many interventions and many different rows in attendance. The tables are not really related to each other.

Using a date slicer, I  select all records in intervention that have an end date in the selected range. For each intervention ID, I then need to use the end date to see if the customer has any attendance on this date. There may be more than one attendance record that applies. I wanted to show the attendance records where applicable in a table. The measure provided in the original response does resolve this.

My issue now is that I have several similar tables with start and end dates. They relate to KPIS and Some have additional metrics that I may need to sum like hours attended. Again, all are provided to me in a completed format but I'm happy to reformat if that is required.

I also now have a start date on the intervention table. I need to provide the metrics at both the start and end of the intervention.

I'm now wondering if it would be best to

a) create a FACT table individually linking each of these additional dimensions - I guess this would involve several bridge tables table too?
b) create separate FACT tables for each of the dimensions bringing together customer, intervention and individual dimension info. Again, I'm assuming this would require a bridge table and I'm unsure how I would design this to reflect the start and end date in intervention.

c) leave each of the dimensions without any relationships

d) A better idea?!

Any help would be much appreciated - modelling is not my strong point 😞 

Thank you

[WBscooby]

Hi

Does anyone have any further advice on this? Thank you

[amitchandak]

@WBscooby , refer if these approaches can help

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[WBscooby]

Hi @amitchandak

Thank you for replying. These are useful videos!

I'm assuming that with the first two videos, I would not create a relationship between the tables?

I have looked at the tables way but there are sometimes several years between start and end date and I am concerned that this will hugely increase the size of the database given that I have several similar tables. Also, would I need to expand both the Intervention table and attendance table to be able to create the relationship?

Given that I have several similar tables, would it be more efficient to use measures or table methodology?

Sorry for all of the questions, I really would like to ensure my model is efficient!