Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |