Forum Discussion
Helper VCreating a Relationship where the Fact row Must Between Two Values
I have to join two tables based on a few different attributes. I know I can do part of this by using Power Query and concatenating to fields. However the challenge is one of the dimensions varies based on a time frame in the format of a year/month.
Fact table
| Client | Dept | Period | Value |
| AB | 100 | 201901 | 10 |
| AB | 100 | 202002 | 20 |
| ZY | 100 | 201905 | 30 |
Dimension Table
| Client | Dept | Division | Period_From | Period_To |
| AB | 100 | Exec | 201901 | 201904 |
| AB | 100 | F&A | 201905 | 202012 |
| ZX | 100 | PRG | 201901 | 202112 |
I can concatenate Client and Dept on both tables in Power Querty to do a Join but how do I handle if the Period column is in-between the Period_From and Period To in the Dimension table? I know how I would do this in a traditional SQL Join with a between statement but how do I create the relation in a Power BI Data Model?
Any ideas and thoughts?
thanks
Alan
2 Replies
- amitchandak
Super User
One way is you create a new version of the table when you can all period from the between the range .
Refer how the new table is created using sheet and date for dates, you can do same for the period
Refer to these SCD docs
https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/
asjonesThanks for the response, that is an interesting solution. It seems like there should be a better way than creating entry for ever point between tow periods (dates). In my case the default Period_to is 209912. So going from say 201000 to 209912 is a huge nubmer of entries to dynamcially create in a table and deal with....
I do aprpecite the response, would loveto hear if anyone has other alternate ideas.
thanks
Alan
