Forum Discussion
Help using date to define relationships
Hello,
I have two tables with are essentially group information and plan information. Group information has a date column which is eligibility month (Essentially just what each group was eligible for in a given month) and the plan information gives rate information that we assign to a group.
Here is the problem, each PlanID can have multiple effective dates and the Group table only has PlanID as a primary key. I need to create a relationship between these tables where I link on PlanID but bring in the rate information only when the Eligibility Month is >= the effective date from the Plan table.
I was able to get this done by creating the following concatenated field in my Fact table.
Fact Table:
Plan Table:
Calculated Column in Fact table = CALCULATE(CONCATENATEX('tbl_ACIS2', 'tbl_ACIS2'[PlanLink]), FILTER('tbl_ACIS2', [PlanLink]=EARLIER('All Groups1'[ConcatField])&&[EffDt]<=EARLIER('All Groups1'[ELIG_MONTH])&&[EndDate]>EARLIER('All Groups1'[ELIG_MONTH])))
Concat Field / Plan Link = concatenated list of all unique fields (except dates) shared between the FACT and plan table that together could be used to identify a single row.
5 Replies
HI Anonymous,
I'm not so clear for your data structure, can you please share some sample data to help us clarify your requirement?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
No problem, So my Plan_Info table would looks essentially something like below
PlanID Effective Date Tier1 Tier2 Tier3 Tier4 1464956464 09/12/06 82 11 35 56 1464956464 10/05/09 94 14 95 20 1464956464 12/10/15 40 47 7 34 1464956464 10/12/17 48 5 62 86 1677443827 01/16/07 73 84 0 27 1677443827 03/20/12 74 3 39 49 1677443827 11/22/12 37 60 75 50 1677443827 12/04/19 69 83 44 72 3319782607 12/22/06 92 71 94 93 3319782607 08/10/11 42 95 87 77 3319782607 06/28/12 45 72 53 29 3319782607 11/09/18 23 80 66 8 4474974437 02/06/04 62 24 74 71 4474974437 03/22/07 70 96 81 51 4474974437 12/14/12 50 64 51 92 4474974437 08/21/14 6 0 11 68 5176533650 03/31/06 66 67 89 83 5176533650 05/09/11 28 40 34 53 5176533650 05/23/11 79 13 2 99 5176533650 04/13/18 49 66 21 33 6313958995 01/29/07 75 12 30 43 6313958995 05/07/10 95 9 10 10 6313958995 11/22/16 16 92 68 30 6313958995 05/24/19 24 36 71 48 8043260683 09/06/04 18 75 67 26 8043260683 07/24/07 8 28 5 23 8043260683 05/17/10 58 2 92 96 8043260683 07/18/17 91 42 23 81 8920263324 10/12/04 9 15 32 98 8920263324 02/25/08 89 45 4 2 8920263324 01/07/13 76 79 91 40 8920263324 09/29/15 90 94 83 44 8975045425 03/30/06 22 17 12 37 8975045425 03/26/09 43 37 18 47 8975045425 05/21/13 97 81 54 41 8975045425 04/07/17 3 39 43 74 9774465499 05/18/05 15 46 28 55 9774465499 11/13/08 35 93 22 6 9774465499 03/23/11 17 68 9 16 9774465499 01/30/18 65 74 45 25 While my Group_Info table would be something like this
GroupID Plan ID Eligibility_Month 2075691963 2316875640 2/18/2005 2142077074 3487704055 6/29/2005 3446850342 4440022584 7/7/2005 5590245482 6901901306 6/19/2006 2610029237 6751717435 8/16/2006 2075691963 8977671492 4/17/2007 2142077074 9068991673 5/1/2007 3446850342 2658301985 4/7/2008 5590245482 2059846164 6/1/2009 2610029237 8213442143 5/5/2010 2075691963 9918227423 10/22/2010 2142077074 4253883282 6/7/2011 3446850342 5184483566 5/7/2012 5590245482 174829346 6/27/2012 2610029237 7359478356 8/21/2012 2075691963 2317301968 2/1/2013 2142077074 7080384659 8/14/2013 3446850342 1696174375 11/21/2013 5590245482 6216713157 3/13/2015 2610029237 91220658 4/18/2016 2075691963 2988592526 6/23/2016 2142077074 7714390915 8/31/2016 3446850342 1221527952 1/24/2017 5590245482 7514195639 12/5/2017 2610029237 3919087282 10/15/2019 2075691963 6443543037 3/8/2004 2142077074 5607788027 1/4/2006 3446850342 1874958246 10/17/2006 5590245482 7880340501 4/4/2008 2610029237 6026551779 1/8/2009 2075691963 5345824967 6/8/2009 2142077074 8992375625 12/28/2009 3446850342 4009485107 4/27/2010 5590245482 8263478601 2/15/2012 2610029237 9976807196 2/29/2012 2075691963 1027126041 5/10/2012 2142077074 5176979641 3/5/2013 3446850342 510432269 9/4/2013 5590245482 6419516086 7/22/2014 2610029237 8448930941 11/9/2015 2075691963 6208136835 12/25/2015 2142077074 9438047362 3/31/2016 3446850342 9371285721 4/1/2016 5590245482 1776205470 5/4/2016 2610029237 5730025069 5/11/2017 2075691963 7978786950 11/23/2017 2142077074 3593229113 3/1/2018 3446850342 9365954196 1/7/2019 5590245482 1122236484 8/29/2019 2610029237 2081440286 12/5/2019 So in SQL Server I was connecting these two tables by a 1:1 on PlanID and then setting the eligiblity month to be greater than or equal to the effective date.
HI Anonymous,
I unpivot your plan table and build relationship based on 'planid' column, then write a measure to calculate result:
Measure = VAR currDate = MAX ( GroupInfo[Eligibility_Month] ) VAR planList = VALUES ( GroupInfo[Plan ID] ) RETURN CALCULATE ( SUM ( Plan[Value] ), FILTER ( ALLSELECTED ( Plan ), [PlanID] IN planList && [Effective Date] >= currDate ), VALUES ( Plan[Type] ) )I also test on your sample data but it seems like no records matched.
Regards,
Xiaoxin Sheng