Forum Discussion
Helper IIIData Model - Multiple tables with Start and End Dates
Helper IIIHi
Thank you for your response! I apologise, I don't think I've been very clear.
There is only one Episode table but I have multiple events tables. A better way of describing may be as event history e.g.
1) Address - This is address history. A client can have multiple addresses with a start and end date if they move
2) School history - A client can have multiple rows if they move schools, all with a start and end date
Etc
All of the event tables have start and end dates but also have different columns attached to them so I'm not sure I should append them?
Thank you
Super UserYou don't describe Events table in the previous model description...
Date Calendar
Client Table: Client ID, Client Name etc
Client Episode: Client ID, Epidsode ID, Episode Start Date, Episode End Date
Client Addresses: Client Address, Address ID, Address Start Date, Address End Date, Address
Hard to give an option without seeing the event tables and knowing the meaning. You can either append and merge with episodes, which will give you more rows, but less columns or merge events to episodes, in which case you end up with a lot more columns...
With the dates, you could later:
a) multiple start and end date tables
b) one dat table, and use inactive relationships, which you will activate later on in DAX measures (using USERELATIONSHIP)...
- WBscooby
Hi
Sorry, I think I'm being unclear in my description. It may be better to describe the tables as containing history, so the Address table is Address history. I have several tables that contain rows of data with a start and end date for each client but they are all quite distinct tables so I cannot append them. I
For each of the tables, I need to find the events that are 'open' at the episode end date for each client.
I have tried to create a sample file to demonstrate as the data I am working with is very sensitive.
Thank you
SampleFile.pbix