issues with relationships

[Marshall10488]

So i am building a report that will showestablishment (number of empoloyees and FTE etc.) for areas in our service.

I have a list of all staff, their areas, position and their FTE on a sheet called Establishment. I am building a sheet, currently called sheet3 that has how many FTE are allocated to each area for each position.

I want to show on the report, when i use a slicer to filter by area or sub team, i want it to show the FTE allocated next to each job role. I currently have it so it does that but it is the Sum of FTE for that job role and not sliced for each area/subteam.

It appears i can only have one active relationship and i think this is what the issue is. I think i need to have active relationships to all the options for the slicers too? if anyone has any idea that would be greatly appreciated.

Below, dashboard giving an idea of what im looking for and the sheet3 headers which match those of establishment (except for names etc.)

[PVO3]

I'm having a hard time trying to figure out what you are exactly looking for. I think you want to to filter by Area, Subteam and Job Role. 

Could you please share a screenshot of your data model with relevant fields & a desired result? 

[Marshall10488]

so the slicers filter the establishment data by those columns.

The allocated table (sheet3) has the same columns so i need those slices to also apply to that table. (i think)

[v-cgao-msft]

Hi @Marshall10488 

PVO3 is right.  A good model is half the battle, and Power BI prefers to use a star schema when modeling.

Dealing with many-to-many relationships. This requires some modifications to the model to allow the filtering to pass correctly, which may affect calculated columns or measures etc. that have already been created. You could try creating DimTables for [Area], [Sub team], [Type] and create relationships with the 'Estimate' table.

Many-to-many relationship guidance - Power BI | Microsoft Docs

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[PVO3]

Thanks for your screenshot. By the looks of it, you will problably encounter a lot of other issues in the near future. I would recommend to develop a star schema to have a solid foundation. Otherwise you'll be problem solving all kind of specific issues.