Help Structuring Table - cleaning up my mess

[bengia]

Hello,

I have been under alot of pressure to get dashboards/reports out and also upskill my self on Power BI.

Whlile there has been a very positive uptake of these, i need to do ALOT of clean up work on my data structure.

This is what my relationships look like. shocking i know.

What i need help on is what is the best relationship to create. 

Lets start simple.

1. I have a Multiple closing balance report which is the main report, i download this at the start of every month. I want to be able to track changes in employees numbers, pay increases, etc etc
2. I have a Attrition report, which i update/refresh every month so i can track attrition trends etc.
3. I have a Hire Report which is similar to Attrition report.

here are the fields for each report:

Closing Balance (i download a copy every month and create a new table - so i can review changes) - i would like someway to create a filter so i can filter by month.

Attrition Report (i only download once and refresh the table)

Hire Report (same as above)

I have created so many relationships etc to make the data present the way I want, so I still managed to achieve the outcome needed but as you can see the relationships are a mess.

Thankyou so much!!!

Ben

[v-yuta-msft]

Hi bengia ,

What is your fact table and dimensional tables, based on your diagram, it seems like table "Closing Balance" has many-to-one relationships with other tables. And you want to filter or achieve values from other dimensional tables in "Closing Balance", right? So I would recommend you to re-organize your table relationship in a start schema, please refer to: https://en.wikipedia.org/wiki/Star_schema. In this senario, make sure that values in key columns is unique in your dimensional tables.

Regards,

Jimmy Tao

[Stachu · Accepted solution]

I'd start with putting all the closing balances in a single table

for comparison I'd just add new column to each table that would specify the period it relates to (e.g. Q1 FY1, Q1 HY1 etc. all in one column)

if you have any other examples of tables sharing the same structure but being separated I would think about putting them together as well

[bengia]

Tthanks so much for reccomendation of merging into a single table.

ive created a single table and now its worked for me. i can now trend data and it looks flash.

thanks!