Forum Discussion
Create relationship: 1 date from 1 table being between 2 dates in 2 columns in another table
I found this thread. Pretty helpful in walking you through the processs in how to expand a table based upon two different date fields. Only thing to add is to change your field names you're referencing in the formula!
Pretty easy.
Community ChampionHi Anonymous,
If you were to create a list of dates for each row in your 'Eligibility Data' table (From Eff Date to Term Date) and then expand them into new rows, would that result in too many rows to handle or would it be managable?
Br,
J
Thank you for the quick response.
I'm not sure I understand exactly what you mean though.
You're asking me to create a new row for each date between the Eff Date and Term Date for each existing row?
If that is the case...for example this one Business has roughly 100 members. One member has an eligibility range 3 years. So that one expansion would grow to 365 x 3, or 1095 rows...for just that one member.
Did I interpret that correctly?
I should also note that one member can show up in the eligibility table more than once...meaning they could have had coverage for a year...quit, and then came back two years later to have coverage for another year and were fired. So they would show up twice in the table as having two serarate rows initially.
- tex628
Generally when you are working with date-ranges the easier way to work is by expanding each range to include a row for each day in the interval. This ususally works really well when you have a database of project or similar with start and end-dates.
The issue with this is that you will increase the amount of data dramatically, so it really depends on how much data you're starting out with and how long the intervals are.
There is another way to do this which would mean that you create an inactive relationship to Term date and then use USERELATIONSHIP() to get the Term date which you can then use as a filter in calculations. But this can get messy quite fast and since you can have more than one instance for each person in the eligibly table I'm seeing quite alot of issues already! 🙂
Either way it doesnt hurt to try anound and see what works!
Br,
J
Can you assist me in how I should go about "Expanding" my Eligibility Table as you suggested? I'm willing to give it a shot.
"If you were to create a list of dates for each row in your 'Eligibility Data' table (From Eff Date to Term Date) and then expand them into new rows, would that result in too many rows to handle or would it be managable?"
I've never done this before. Essentially, what I think will end up happening is that an existing row with an [Eff Date] of 1/1/2019 and a [Term Date] of 1/3/2019 will populate with three rows. For example, a starting table in Power BI like this:
would turn into this?:
If this is what you mean...can you assit in walking me through how to transform my data to this?
PaulDBrownAnonymous
Sorry to barge in this late in the discussion... I may be overlooking something here but I would assume that the idea behind your date table is:
1) Ensure it covers the whole range of dates possible within your tables, with unique and continuous dates. I guess the question is which column has the earliest date and which has the latest date.
You can work around this by referencing the tables to three new tables (one for each of the dates columns you need), removing all columns except the dates columns. Append the tables to create a single table ("New Dates Table") with one column for the dates ("Date"), remove the duplicates and order ascending.
You can then use the following code in a new query in Power Query to create your date table and to obtain a list of unique, continuous dates based on the MIN and MAX dates in this new table:
let ChangedType = Table.TransformColumnTypes(#"New Dates Table",{{"Date", type date}}), MaxDate = Record.Field(Table.Max(ChangedType,"Date"),"Date"), MinDate = Record.Field(Table.Min(ChangedType,"Date"),"Date"), DaysElapsed = Number.From(MaxDate-MinDate), DateList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"and build any further columns you may need to have a working date table.
2) Create relationships with the dates fields in each table which you will be using most.
3) For calculations/filtering which require the date field not included in the active relationship, create an inactive relationship and use the USERELATIONSHIP function.
I apologise if I have misunderstood what your request is.