Forum Discussion

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
3 years ago

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

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

 

3 Replies