Filter Table based on field in Second Table

[Gottijay2000]

i have two tables "Issues" and "Users" with a many-one relationship. Many on the issues table and one on the users Table, which is achieved by adding a lookup column (userid) into the issues Table. 

Users

Fields - userid, Name, Gender

Issues

Fields - issueId,userid,date_raised

i want to show the issues for only when the Gender of the user is Female. 

i tried this but i keep getting errors. any suggestions about how i can write this syntax would be appreciated

Filter(Issues, userid = Filter(users, Gender="Female").userid)

[RandyHayes · Accepted solution]

@Gottijay2000 

You might want to consider using the In operator for this.  Try this for your formula:

Filter(Issues, userid in Filter(users, Gender="Female").userid)

I hope this is helpful for you.

[emh · Accepted solution]

Hello,

There are two options that can work. 

1) If you are using sql tables as your underlying data source, then you could create a view which performs the join in the CREATE VIEW definition:

SELECT u.userid, u.Name, u.Gender, i.date_raised

FROM dbo.Users u

INNER JOIN dbo.Issues i

ON u.userid=i.userid

2) You can perform the join within PowerApps using ShowColumns and AddColumns and then use this table result as your data source

ShowColumns(
    AddColumns(
        Users,
        "date_raised",
        LookUp(Issues, userid = Users[@userid], date_raised)),
    "userid", "Name", "Gender", "date_raised")

Hope this helps!

[Gottijay2000]

When i tried using the IN operator, this time i get an error with the "=" operator in the inner Filter function(ie Gender=Female"). the error say that it is an "invalid argument type"

[RandyHayes]

@Gottijay2000 

Check that your formula reads exactly as this:

Filter(Issues, userid in Filter(users, Gender="Female").userid)

That statement should work just fine (other than a warning for the potential for delegation errors on large data sources)

[Gottijay2000]

many thanks, the gender field was actually a choice field so i changed the value from Gender to Gender.Value

[RandyHayes]

@Gottijay2000 

So this is a SharePoint choice column...yes, you will need the .Value

The solution you marked as your accepted solution for your question seems to indicate that you are working with SQL - perhaps you marked the wrong solution?