How to filter data in one table using an item in another table column?

[justlearning50]

Hello,

I was wondering how I can filter a results table based on a team name in the team table. The relationships are set up as shown in the simplified diagram below. 

The arrow head and red dot indicate the 'many' side of the relationship. The user/team is a many to many relationship with the position table combining both. (it isn't my data, I'm just trying o create reports from it)

My question is how do I select items from the results table based on a team in the team table? Because of the relationships I cannot use the related function, and relatedtable only allows me to select the table not a filtered column in the table. Is there is a way to filter based on this schema. 

The information I got with the data suggests the position:team relationship was a 1:many, but it didn't work that way when I connected to the datasource. If this was the case, my understanding is that the Related function should work. Is that correct? 

Also is there any way to filter the data for the latest date in the system when the data is being imported/refreshed, rather than finding the latest result via a measure? The results table seems to be duplicating the data every day so for each test I have a row for every day since it was released - so just getting gigger and bigger all the time. 

Thanks so much for your help.

[speedramps · Accepted solution]

If the source data has lots of fields in the Team table (eg Country, Region and Manager name)

that you need then you may decide to have a seperate Team table in your Power Bi data model.

The Teams table should have one unqiue record per Team and no duplicates and no missing Teams.

The Teams tabel should then have a one to many relationship with the Positions table.

Your dataset will the start to look more like snowflake rather than a star.

It is not essential to have a Team ID, but you will need unique Team column. For examplle the Team Name to build the relationship.

You are unlikely to have milions of rows in Positions. So dont be affarid to merge the Teams columns ( (eg Country, Region and Manager name) into Positions. It wont significanty increase the size of your Power BI dataset, and may make it easier for you to understand. Less is more! Keep it simple.

Power BI Developers often find themselves in the situation "my company doesn't own the database".

They use Power Query to reshape the data into dataset  for easy reporting.

Please can you accept the solution and close the ticket. I have provided a link on how to data model.

Raise a new ticket if you have a specific question. Thanks

[speedramps]

It is best pratice to use Power Query to reshape your source data in Star Schema

Learn how here https://learn.microsoft.com/en-us/power-bi/guidance/star-schema 

Consider having a simpler Position table showing who is in each team

Note user U1 is is more than one team 

Then have a one to many relationship from Postion to the the User table.

The user table must have unique rows for each users, and no duplicates.

Then have a one to many relationship from User to the Results table, because a user can have more than one result.

The Result table must have unique rows for each Result , and no duplicates.

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[justlearning50]

Thanks for coming back to me @speedramps . So in my position table i have an id column, user_id and team_id columns. Are you suggesting that I should merge the team and position table so I have the team name in the position table? Will it not still be a many:1 relationship with the user table? as one user can have many positions, but any single position will only have one user. To have a proper star schema, I think i would need the team id in the results table, but it isn't there, and my company doesn't own the database. I just use the data. However I may be missing something in your answer. thanks again for your time.