Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

No problem,  So my Plan_Info table would looks essentially something like below

 

PlanIDEffective DateTier1Tier2Tier3Tier4
146495646409/12/0682113556
146495646410/05/0994149520
146495646412/10/154047734
146495646410/12/174856286
167744382701/16/077384027
167744382703/20/127433949
167744382711/22/1237607550
167744382712/04/1969834472
331978260712/22/0692719493
331978260708/10/1142958777
331978260706/28/1245725329
331978260711/09/182380668
447497443702/06/0462247471
447497443703/22/0770968151
447497443712/14/1250645192
447497443708/21/14601168
517653365003/31/0666678983
517653365005/09/1128403453
517653365005/23/117913299
517653365004/13/1849662133
631395899501/29/0775123043
631395899505/07/109591010
631395899511/22/1616926830
631395899505/24/1924367148
804326068309/06/0418756726
804326068307/24/07828523
804326068305/17/105829296
804326068307/18/1791422381
892026332410/12/049153298
892026332402/25/08894542
892026332401/07/1376799140
892026332409/29/1590948344
897504542503/30/0622171237
897504542503/26/0943371847
897504542505/21/1397815441
897504542504/07/173394374
977446549905/18/0515462855
977446549911/13/083593226
977446549903/23/111768916
977446549901/30/1865744525

 

While my Group_Info table would be something like this

 

GroupIDPlan IDEligibility_Month
207569196323168756402/18/2005
214207707434877040556/29/2005
344685034244400225847/7/2005
559024548269019013066/19/2006
261002923767517174358/16/2006
207569196389776714924/17/2007
214207707490689916735/1/2007
344685034226583019854/7/2008
559024548220598461646/1/2009
261002923782134421435/5/2010
2075691963991822742310/22/2010
214207707442538832826/7/2011
344685034251844835665/7/2012
55902454821748293466/27/2012
261002923773594783568/21/2012
207569196323173019682/1/2013
214207707470803846598/14/2013
3446850342169617437511/21/2013
559024548262167131573/13/2015
2610029237912206584/18/2016
207569196329885925266/23/2016
214207707477143909158/31/2016
344685034212215279521/24/2017
5590245482751419563912/5/2017
2610029237391908728210/15/2019
207569196364435430373/8/2004
214207707456077880271/4/2006
3446850342187495824610/17/2006
559024548278803405014/4/2008
261002923760265517791/8/2009
207569196353458249676/8/2009
2142077074899237562512/28/2009
344685034240094851074/27/2010
559024548282634786012/15/2012
261002923799768071962/29/2012
207569196310271260415/10/2012
214207707451769796413/5/2013
34468503425104322699/4/2013
559024548264195160867/22/2014
2610029237844893094111/9/2015
2075691963620813683512/25/2015
214207707494380473623/31/2016
344685034293712857214/1/2016
559024548217762054705/4/2016
261002923757300250695/11/2017
2075691963797878695011/23/2017
214207707435932291133/1/2018
344685034293659541961/7/2019
559024548211222364848/29/2019
2610029237208144028612/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.

 

 Untitled.jpg

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] )
    )

17.png

 

I also test on your sample data but it seems like no records matched.

 

18.png

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.