Create relationship: 1 date from 1 table being between 2 dates in 2 columns in another table

[Anonymous]

Hello Power BI community,

I've been working in Power BI for about 1 month now and have learned a lot from this community.  However, I have a current project I'm working on that I'm struggling mightily.  On the surface I know what I need to do in order to accomplish it but have failed to find any resource/thread that meets my needs.  I have stumbled upon similar situations but nothing exactly like it so I haven't made much solid progress other than finding dead ends.

Background:

1) I have 2 separate excel tables I'm bringing in to Power BI

a) Table 1:  'Claim Data'

b) Table 2:  'Eligibility Data'

2) The 'Claim Data' table contains member healthcare claims (for example a healthcare claim is when a member broke their arm, had a baby or went to the ER for stitches).  Each claim (row) has supporting data behind it such as Customer ID.  Each claim (row) contains columns with various types of descriptions/information of the type of claim they had, such as the amount of money they paid, the amount of money the insurance paid, deduction amount etc.  But most importantly, this table contains the 'Claim Data'[From Date] (the date the member actually received actual care).

a) One customer can have many different claims.

b) It's possible that one customer has multiple claims on the same 'Claim Data'[From Date]

c) It's also possible that a customer that has coverage (is eligible) and included on the 'Eligibility Data' table is not included in the 'Claim Data' table

3) The 'Eligibility Data' table contains the type of coverage each member had, their age, Customer ID...but also; and most importantly the date range each member was eligible to receive healthcare coverage.  This date range is broken up between two different columns in this table.  The start date (or the date the member became eligible for coverage) is indicated by 'Eligibility Data'[Eff Date] while the end date (or the date the member's coverage ended) is indicated by 'Eligibility Data'[Term Date].

Goal:

1) My end goal is to create many visuals where I can slice and dice both the 'Eligibility Data' table and 'Claim Data' table at the same time.  Some of these visuals will be solely populated from the 'Claim Data' table, some from the 'Eligibility Data' table and other visuals using fields from both the 'Eligibility Data' table and the 'Claims Data' table.

a) I ultimately envisioned using a 'date' slicer with its field stemming from a 'Date Table'.

i) My problem is that I don't know if a 'Date Table' can be built/used in this manner.

In Short:

This might be the most helpful part of this post in expressing what I'm trying to do here:

1) For example:  I'd like to have one Stacked Column Chart where it lists the Number of Members that were eligible by month for a given year.  On that same page, I'd like to have another Stacked Column Chart where it would list the number of claims processed for that same span of time.  I'd like to have a slicer at the top of the page where I can change the date range and have both graphs update automatically.

2) I have a lot of visuals I need to create but having this 'Date Range' table is absolutely crucial and I'm not even sure it's really possible.

Relationships:

1) Here is a screen shot of my current relationships:  I have a Date Calendar already built but it is only creating relationships between:

• 'Date'[Date] --> 'Claim Data'[From Date]
• 'Date'[Date] --> 'Date'[Eff Date]

2) It is completely ignoring the 'Date'[Term Date] field...ultimately not considering date ranges at all.  So it is flawed and not what I need but this is as far as I have gotten.

'Claim Data'[From Date] should fall between 'Eligibility Data'[Eff Date] and 'Eligibility Data'[Term Date]

3) As you can see, I've also created a 'Members' Table...with Many to One relationships between it and both the 'Claim Data' Table and 'Eligibility Data' Table.  I figured I might need this as some point.

 

Please let me know if this is more involved than I think it is.  Although I've been struggling, I can't imagine this is really all that complicated.  I'd have to think that this type of task is common for many.

Please help!

Thank you in advance,

Andrew

[Anonymous · Accepted solution]

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.

https://natechamberlain.com/2018/08/08/how-to-add-rows-for-dates-between-start-and-end-dates-in-powe...

[tex628]

Glad to hear you figuered it out! Let me know how it goes- 

Br,
J

[amitchandak]

I would suggest that you populate eff date into the claim table. You can get a column from one table to another based on required no of condition

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

If I populated the 'Eligibility Data'[Eff Date] into the 'Claims Table'...how would I account for the 'Eligibility Data'[Term Date].  I don't believe bringing in the 'Eligibility Data'[Eff Date] into the 'Claim Data' Table would accomplish anything.  I still need to account for the 'Eligibility Data'[Term date].

Also, as stated before, a Member ID can populate many times in both tables.  Not sure how that would impact what your solution suggests.

A claim is only eligibile if the 'Claim Data'[From Date]  is between the 'Eligibility Data'[Eff Date] and 'Eligibility Data'[Term Date] for that Member ID.

[tex628]

Hi @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

[Anonymous]

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?

[PaulDBrown]

@Anonymous 

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.

[Anonymous]

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