Forum Discussion
Relationships between tables: A date being between a date range
Hi,
I've only been using Power BI for roughly 2 weeks and I'm still learning. I'm having some trouble wrapping my head around this issue. Any help is much appreciated. Thank you!
Problem:
Attempting to create relationships between two tables (Table 1: 'SQL Claim Pull' and Table 2: 'SQL Eligibility Pull'). The SQL Claim Pull table has one date column; the "Srvc From Date" column. The SQL Eligibility Pull table has two date columns, "Eff Date" and "Term Date".
I'm trying to create the relationship if:
1) The ('SQL Claim Pull'[Srvc From Date]) column is between the date range of ('SQL Eligibility Pull'[Eff Date]) and ('SQL Eligibility Pull'[Term Date']
Both tables (Table 1: 'SQL Claim Pull' and Table 2: 'SQL Eligibility Pull') share the same two columns to link through:
1) Member ID
2) Plan ID
Nuances:
1) In both tables, there can be multiple iterations of Member ID (it can appear multiple times)
2) In both tables, there can be multiple iterations of Plan ID (it can appear multiple times)
Here is my current relationship map:
I've made a 'calendar' table for different problems for different projects before but I don't think that will solve my issue. Because I still can't figure out how to handle the one date in between two other dates in another table problem.
Please let me know if I didn't do a very good job of explaining this. I can respond back with more detail. I'll be closely monitoring the thread for help. And thank you again!
3 Replies
- kentyler
Solution Sage
I'm not sure, but it looks like "claim pull" is your tentative "fact" table.
Is pulling a claim an event, with a date ?
Then we need to think about your dimensions. I think you have an unexpressed dimension which would be a "Member" or "Client" table. The "Group" that person is in should probably be collapsed into that table rather than being put into the data model as a separate table.
Ideally we are aiming for a "star" schema, where fact tables have relationships to dimension tables.
It looks like you have 3 fact tables. Claim, Eligibility and Member Months.
A Member table would be a shared dimension that would have a one to many relationship with all three fact tables, as would your date table.
You are running at a month granularity, so filtering on a date would then filter all three fact tables.If you wanted to ask, does a claim in the claim table fall inside the eligibility dates for the member, that would be a measure that would return a true false answer for each claim record. You could put that measure in a calculated column or use it in other measures to calculate sums and averages.
This can all be complicated. If you'd like to jump on a screen share and talk it over email me and propose a day and time ( [email protected])I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result to check on your steps along the way.
Thank you for the response. I'm going to watch those videos and try my best to apply what you said. I'm sure the videos you linked below will answer some questions but create even more haha.
1) Consider the "Claims pull" the transactions table. So, I think I understand when you refer to it as a "fact table" now.
2) So yes. A claim is like an event. It populates with several different dates...but the date I'm interested in is its "Srvc From Date".
3) What do you mean by unexpressed dimension? Do you mean I should make a separate 'pass through table' (like my GroupID or Group Name pass-through tables)? In order to find each unique Member ID? I think you refer to these as 'Star Schema tables".
4) Yes. There are 3 fact tables: Claim, Eligibility and Member Months.
5) I can create a Member "Star Schema" table...but it will only be able to have a one-to-many relationship with Claims and Eligibility as those have the Member ID column. The Member Months table does not have Member ID. But it does share the GroupID column with both the Claims and Eligibility tables. That's the only column that is shared between all three.
6) Exactly. "If you wanted to ask, does a claim in the claim table fall inside the eligibility dates for the member..." those are exactly the types of metrics I'd like to work with.
Thank you for the help. Can you answer these quick questions? With those answers and the videos, I might be able to figure it out. If I still struggle, I'll reach out via email and thank you again sir!
- Andrew
I should add. My main goal is to be able to create visualizations based upon a monthly basis. In other words...mostly graphs Jan, Feb, Mar, April.... for the year. Breaking down the analytics by month.
Essentially, if the Claim Srvc From Date is between the Eff Date and Term Date; then there should be a relationship based on both Member ID and Plan ID.
