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 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
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/
Thanks 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
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |