Help using date to define relationships

[Anonymous]

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.

[v-shex-msft]

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

[Anonymous]

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.

 

[v-shex-msft]

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

[Anonymous]

Thanks for the reply,

when you said you built the relationship based on PlanID, did you do that in query editor?  I ask because I cant create that relationship in DAX because it would be many to many.

The data I provided earlier was just random sample data so I am not surprised there were no matches.  I have a pbix file I can provide with live data but do not know how to share that file.

[Anonymous · Accepted solution]

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.