Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 | |
userid | yearStarted |
1 | 2010 |
2 | 2010 |
3 | 2011 |
4 | 2011 |
Fact | ||||
FactID | UserID | TransactionDate | PeriodID | Measure1 |
1 | 1 | 28/03/2011 | 3 | |
2 | 1 | 30/03/2011 | 3 | 8 |
3 | 2 | 1/8/2010 | 2 | 4 |
4 | 3 | 15/10/2011 | 6 | 2 |
5 | 4 | 30/12/2012 | 8 | 8 |
6 | 1 | 10/5/2011 | 3 | 7 |
7 | 2 | 28/02/2011 | 3 | 2 |
8 | 3 | 30/05/2012 | 7 | 9 |
9 | 1 | 31/10/2010 | 4 | 7 |
10 | 4 | 20/01/2011 | 1 | 6 |
DimPeriod | ||||
PeriodID | PeriodYear | YearPart | StartDate | EndDate |
1 | 2010 | 1 | 1/1/2010 | 30/06/2010 |
2 | 2010 | 2 | 1/7/2010 | 31/12/2010 |
3 | 2010 | 3 | 1/1/2011 | 30/06/2011 |
4 | 2010 | 4 | 1/7/2011 | 31/12/2011 |
5 | 2011 | 1 | 1/1/2011 | 30/06/2011 |
6 | 2011 | 2 | 1/7/2011 | 31/12/2011 |
7 | 2011 | 3 | 1/1/2012 | 30/06/2012 |
8 | 2011 | 4 | 1/7/2012 | 31/12/2012 |
Solved! Go to Solution.
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] )
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] )
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.