Relationships between tables for Graphs

[Anonymous]

Hi All

Help required if possible please - but note I am a pleb, so appreciate patience as this is probably something simple!

I want to create a link between 2 groups of people - those who have joined and those who have left, but based on the place of work.

When I then press on a graph showing a count of those who have left, it populates the graph for those who have joined so I can then see both! Simple! Right.....well no. I cant seem to get that relationship between the names of the company in both lists to fit together.  I have tried simply linking the 2 tables, but it wont allow me to use this, so created a fact table [another suggestion via this forum] but it isnt playing ball in both ways

Advice welcome - feel free to laugh at my basic request....but again, help most welcome as I am smashing my head against a wall with this!

thank you!!

need to link green to blue & vice versa if possible

[HotChilli]

Hello,

please don't put yourself down - we're all learners at different stages.

I can see that you've taken a design decision to split your data into 2 similar tables with a reasonably complex set of relationships to slice and dice that data.

I'm not really a fan of such designs (it requires a thorough understanding of the relationships and what you actually need the data for) Personally, I would probably combine the data and have another dimension table called Type (leaver or joiner) which would allow slicing.

Anyway, let's deal with the model as is.

One thing that is concerning is that it looks (from the 'edit relationships' picture that you posted) that you have duplicate records in your Employer table.  Can you confirm and clean this table up?

[Anonymous]

Hi HotChilli

thank you for your reply - i am still learning how Power BI works, so this may not be a conscious design choice....more expecting this to be the best method to link these fields.

There are no duplicate records in the EMPLOYER TABLE but would be in the joiners and leavers tables.

I had to seperate the joiners\leavers as the data we extract from our queries as we had issues when attempting to combine the 2, so we decided to keep seperate to ensure accuracy.

I must also say that the data is automatically updated, which in turn then automatically updates via Power BI - so i could manually combine them, but i am doing my best to regulate all dashboards so want to minimise my involvement once in place.

[HotChilli]

I still think there is a problem with the Employer table.  Here's my thinking :

1. The relationship screen shows duplicate records for The Royal Bank

2. It has defaulted to Many to Many relationship. It's a simple table and I can't think of why a many-to-many relationship would be appropriate here.