Forum Discussion
Multiple relationships between tables
I've looked online and messed around but still need assistance. I want to maintain my "Store" relationships but also add relationships for "Week.2". I'm using these for filtering however, the filters aren't flowing through for the Weeks at the moment. So I created a separate table with basic weeks (ie 1-52) and try to set it but it doesn't work ("Store" filter works fine):
4 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Main 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 etcPlease 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):
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.
