cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

dynamically Calculating a foreign key for fact table using Dax

I have a Period dimension table that i wish to link with my fact table. 
There is no foreign key column in the fact table for Period. I can add it during the load, but would like to see about doing it dynamically using dax and could use some help.

 

I have some sample data below. Dimuser links to Fact on UserID.
DimPeriod will  link to Fact on PeriodID, but PeriodID in fact table needs to be calculated.

 

Its not as simple as saying where transactionDate between Period start & End Date. this is because periods can have same start and end dates, but differ based on when the user joined. i.e. 

If a user joins in 2010 and creates a transaction on 31/12/2011, it will be in period 4, but if a user joined in 2011 and had a transaction on the same day, it would be in period 6.

 

I have manually calculated PeriodID in the fact table below but am struggling to do this in DAX.

I hope that makes sense. If i can clarify anymore please let me know.

 

 

DimUser 
  
useridyearStarted
12010
22010
32011
42011

 

Fact    
     
FactIDUserIDTransactionDatePeriodIDMeasure1
1128/03/20113 
2130/03/201138
321/8/201024
4315/10/201162
5430/12/201288
6110/5/201137
7228/02/201132
8330/05/201279
9131/10/201047
10420/01/201116

 

 

DimPeriod    
     
PeriodIDPeriodYearYearPartStartDateEndDate
1201011/1/201030/06/2010
2201021/7/201031/12/2010
3201031/1/201130/06/2011
4201041/7/201131/12/2011
5201111/1/201130/06/2011
6201121/7/201131/12/2011
7201131/1/201230/06/2012
8201141/7/201231/12/2012
1 ACCEPTED SOLUTION
Community Support
Community Support

@wilson_smyth,

 

You may use DAX below to add a calculated column.

Column =
MAXX (
    FILTER (
        DimPeriod,
        DimPeriod[PeriodYear] = RELATED ( DimUser[yearStarted] )
            && DimPeriod[StartDate] <= 'Fact'[TransactionDate]
            && DimPeriod[EndDate] >= 'Fact'[TransactionDate]
    ),
    DimPeriod[PeriodID]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

@wilson_smyth,

 

You may use DAX below to add a calculated column.

Column =
MAXX (
    FILTER (
        DimPeriod,
        DimPeriod[PeriodYear] = RELATED ( DimUser[yearStarted] )
            && DimPeriod[StartDate] <= 'Fact'[TransactionDate]
            && DimPeriod[EndDate] >= 'Fact'[TransactionDate]
    ),
    DimPeriod[PeriodID]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Memorable Member
Memorable Member

Why do you need to calculate in Dax?

 

Given the link is not simple it would make more sense to have on the fact. Remember the data is compressed so if you not got many values it will not table up much space even with a large number of rows.

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors