Power App - How to use text value for Column Name in Formula?

I am creating an application that requires showing unique records based off of the current user's department. I have two tables, one that has records to be reviewed and the other that has team members by department. I am able to fetch the department of the user using LookUp, this returns the department name as text. I want to feed into Filter to extract records meeting a Boolean condition [Filter(Table, Column = false)] where the column name IS the same department name.

Is there a way to change this text to where Filter will recognize the text as the column name?

Or some way I can extract that column from the text, then Filter on the resulting table?

My table looks like this:

Project	Sales	Marketing
Proj1	true	true
Proj2	false	true

The department I want is Sales. The Project I want is Proj2 where Sales = false.

The following returns "Sales":

LookUp('Employee Departments','Employee Email' = Office365Users.MyProfileV2().mail,Department)

Tried to feed into Filter where Sales is a column with Boolean values:

Filter('Records for Review',LookUp('Employee Departments','Employee Email' = Office365Users.MyProfileV2().mail,Department) = false)

Want to extract all records for which the value is false in the column with the department name.

Solution

There isn't any direct way to refer to the column name dynamically. But what you can do is, put conditions based on which you want to filter the data. Your filter query should be something like this:

Filter(
    'Records for Review',
    Switch(
        LookUp('Employee Departments','Employee Email' = Office365Users.MyProfileV2().mail,Department),
        "Sales", Sales = false,
        "Marketing", Marketing = false,
        false // Default case if no match for department
    )
)

But the only drawback would be, this way would give you a delegation warning.

If you want to avoid the one way would be writing filters for each department, but it could be lengthy though (based on your number of departments) and in case of you want to put some additional filter parameters, then you need to add it into each filter query:

Switch(
    LookUp('Employee Departments','Employee Email' = Office365Users.MyProfileV2().mail,Department),

    "Sales", // Filter in case of sales department
    Filter('Records for Review',Sales = false),
    
    "Marketing", // Filter in case of marketing department
    Filter('Records for Review',Marketing = false)
)