Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |