Forum Discussion
Multiple relationships between tables
Thanks. I've resolved my issue by creating 2 filters and combing them as a group and selecting "sync slicers"
Main table Store Directory which I connect all tables (Key):
| Store Number | Store Name |
| 1 | Storename1 |
| 4 | Storename2 |
| 5 | Storename3 |
| 7 | Storename4 |
| 13 | Storename5 |
| 15 | Storename6 |
| 16 | Storename7 |
| 22 | Storename8 |
| 23 | Storename9 |
WK Table, which I've created to allow tables to associate wk numbers with:
| Week |
| 202201 |
| 202202 |
| 202203 |
| 202204 |
| 202205 |
| 202206 |
| 202207 |
| 202208 |
| 202209 |
| 202210 |
| 202211 |
| 202212 |
| 202213 |
| 202214 |
| 202215 |
| 202216 |
| 202217 |
| 202218 |
| 202219 |
| 202220 |
| 202221 |
| 202222 |
| 202223 |
| 202224 |
| 202225 |
| 202226 |
| 202227 |
| 202228 |
| 202229 |
| 202230 |
| 202231 |
| 202232 |
| 202233 |
| 202234 |
| 202235 |
| 202236 |
| 202237 |
| 202238 |
| 202239 |
| 202240 |
| 202241 |
| 202242 |
| 202243 |
| 202244 |
| 202245 |
| 202246 |
| 202247 |
| 202248 |
| 202249 |
| 202250 |
| 202251 |
| 202252 |
Traffic data example:
| site_name | fiscal week # | fiscal month | fiscal year | traffic |
| 4 | 1 | Feb | 2022 | 60 |
| 4 | 2 | Feb | 2022 | 20 |
| 4 | 3 | Feb | 2022 | 50 |
| 4 | 4 | Feb | 2022 | 41 |
| 4 | 5 | Mar | 2022 | 60 |
| 4 | 6 | Mar | 2022 | 55 |
| 4 | 7 | Mar | 2022 | 66 |
| 4 | 8 | Mar | 2022 | 555 |
| 4 | 9 | Mar | 2022 | 29 |
| 4 | 10 | Apr | 2022 | 60 |
| 22 | 4 | Feb | 2022 | 20 |
| 22 | 5 | Mar | 2022 | 50 |
| 22 | 6 | Mar | 2022 | 41 |
| 22 | 7 | Mar | 2022 | 60 |
| 22 | 8 | Mar | 2022 | 55 |
| 22 | 1 | Feb | 2022 | 66 |
| 22 | 2 | Feb | 2022 | 555 |
| 23 | 1 | Feb | 2022 | 29 |
| 23 | 2 | Feb | 2022 | 60 |
| 23 | 3 | Feb | 2022 | 20 |
| 23 | 4 | Feb | 2022 | 50 |
| 23 | 5 | Mar | 2022 | 41 |
| 23 | 6 | Mar | 2022 | 60 |
| 23 | 7 | Mar | 2022 | 55 |
| 23 | 8 | Mar | 2022 | 66 |
| 23 | 9 | Mar | 2022 | 555 |
| 23 | 10 | Apr | 2022 | 29 |
| 23 | 31 | Sep | 2022 | 60 |
| 23 | 32 | Sep | 2022 | 20 |
| 23 | 33 | Sep | 2022 | 50 |
| 23 | 34 | Sep | 2022 | 41 |
| 23 | 35 | Sep | 2022 | 60 |
| 58 | 3 | Feb | 2022 | 55 |
| 58 | 4 | Feb | 2022 | 66 |
Task table example:
| Store | Week | Hours |
| 1 | 202201 | 12 |
| 4 | 202201 | 5 |
| 7 | 202201 | 6 |
Training table example:
| Store | Week | Hours |
| 474 | 202201 | 9 |
| 530 | 202201 | 5 |
| 530 | 202202 | 1 |
| 586 | 202201 | 20 |
| 568 | 202201 | 60 |
| 9087 | 202201 | 2 |
Stock take table example:
| Store | Week | Hours |
| 1 | 202205 | 4 |
| 4 | 202205 | 6 |
LP table example:
| Store | Week | Hours |
| 558 | 202201 | 36 |
| 558 | 202202 | 36 |
| 558 | 202203 | 36 |
| 558 | 202204 | 36 |
Misc table example:
| Store | Week | Hours |
| 526 | 202201 | 10 |
Etc etc
Please note within Power BI I have tried both formats of WK entry in all tables to see if either works ie:
| 202201 |
| 202202 |
And
| 2022 | 1 |
| 2022 | 2 |
I want "Week filter" to work for all, currently it only works for 1 active relationship:
Below is to represent the main table (key) is for the Store name as I need to utilist this for other data (ie store name table):
- lbendlin
Super User
The 202201 format for the week is preferable.
Power BI would like to see a data model that has dimension tables (things you can filter by) surrounding fact tables (things you can compute) in a star schema or snowflake schema. Dimension tables have primary keys that are unique, and that are pointing to fields in the fact table in a 1:* relationship. Your data model snapshot seems to be lacking these features. Please consider cleaning your data model up so you can arrive at the star/snowflake schema.
Thanks. I've resolved my issue by creating 2 filters and combing them as a group and selecting "sync slicers"
