Dynamic filter using lookups

[keatonbeyer]

I'll start by saying I'm new to PowerBI. I've used Tableau and know I can do what I'm trying in that tool but in PowerBI it seems, less able.

I have a table of projects, those projects have owners.

I have a table of tasks which are associated to projects. Tasks also have owners.

I have a table with users and their managers.

What I want is a filter that will look through all project owners and show those projects, but I also want it to look through all task owners and show those projects where the manager may not manage the project owner, but someone working on the project.

How I'd do this in Tableau. I would create a filter of manager names. Then I would reference that filter in a formula that would do a lookup between both tables and return a true for the line associated to the primary key of the project. The "filter" slicer on the report is really a dummy list, and the actual filter is the formula which only display projects where the primary key=true in the formula.

Does anyone know a way to write formulas in PowerBI in such a way? All I can create a measures but measures appear to only deal with numbers and aren't condusive to doing dynamic lookups.

[v-sgandrathi]

Hi @keatonbeyer,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

Thank you.

[v-sgandrathi]

Hi @keatonbeyer,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[lbendlin]

The preferred approach in Power BI is filter transfer via TREATAS.  LOOKUPVALUE is possible but not considered best practice.

[Rufyda · Accepted solution]

You can achieve this in Power BI using DAX — it just requires a different mindset than Tableau.
Instead of a boolean filter, you can create a measure that checks if a project is owned by the selected manager’s team, either directly (project owner) or indirectly (task owner).
For example:
ShowProject =
VAR SelectedManager = SELECTEDVALUE(Users[Manager])
VAR ManagedUsers = FILTER(Users, Users[Manager] = SelectedManager)
VAR HasDirectOwnership =
Projects[OwnerID] IN SELECTCOLUMNS(ManagedUsers, "UserID", Users[UserID])
VAR HasTaskOwnership =
COUNTROWS(
FILTER(Tasks,
Tasks[ProjectID] = Projects[ProjectID] &&
Tasks[OwnerID] IN SELECTCOLUMNS(ManagedUsers, "UserID", Users[UserID])
)
) > 0
RETURN IF(HasDirectOwnership || HasTaskOwnership, 1, 0)

Then use this measure as a visual-level filter (ShowProject = 1).

It gives the same effect as your Tableau logic — filtering projects dynamically based on the selected manager and their team.

For a similar concept and more details, you can also check Microsoft’s official documentation on Row-Level Security (RLS) in Fabric:
Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rufyda Rahma | Microsoft MIE
https: Rufyda Rahma | LinkedIn
Blog : Rufyda Rahma – Medium

[keatonbeyer]

This is very close, I've tweaked it and it is almost working but I'm having problems with the task ownership piece. I've played with the formula and it does product the correct counts of rows with task owners on a project when I isolate the countrows bit minus the select columns based on user name but the problem is once I apply that and run the whole thing it only selects rows when a person on the manager's team has a task on the project. I want it to still have a count when the person owning the project is outside of the managers team. I think what's happening is the tables are being filtered down so much that the available projects in the "programs" table is already filtered down prior to the count running. The problem is I can't figure out how to get it to go first. 

Show Project =

VAR SelectedManager = SELECTEDVALUE(Managers[Manager])

VAR ManagedUsers = FILTER(Managers,Managers[Manager]=SelectedManager)

VAR HasDirectOwnership = MAX('planview rpt_programs (3)'[tech_prog_mgr]) IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])

VAR HasTaskOwnership =

COUNTROWS(

FILTER( 'planview rpt_raid', MIN('planview rpt_programs (3)'[primary_key]) = 'planview rpt_raid'[parent_task_key]

&& 'planview rpt_raid'[resource] IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])

)

)>0

RETURN IF(HasTaskOwnership|| HasDirectOwnership , 1, 0)

[Rufyda]

Maybe the issue happens because your tables are being filtered too early.
To fix it, use ALL() inside your DAX formula — this removes unwanted filters before the calculation runs.

Show Project =
VAR SelectedManager = SELECTEDVALUE(Managers[Manager])
VAR ManagedUsers = FILTER(ALL(Managers), Managers[Manager] = SelectedManager)
VAR HasDirectOwnership =
'planview rpt_programs (3)'[tech_prog_mgr]
IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])
VAR HasTaskOwnership =
COUNTROWS(
FILTER(
ALL('planview rpt_raid'),
'planview rpt_raid'[parent_task_key] = 'planview rpt_programs (3)'[primary_key] &&
'planview rpt_raid'[resource]
IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])
)
) > 0
RETURN IF(HasDirectOwnership || HasTaskOwnership, 1, 0)


ALL() makes sure your FILTER sees all rows and ignores the existing context filters.
This way, the formula correctly shows projects owned either directly by the manager’s team or by anyone who owns a task within that project.

[lbendlin]

Remember that your FILTER is applied on top of the current filter context. Better use

VAR ManagedUsers = FILTER(ALL(Managers),[Manager]=SelectedManager)

[keatonbeyer]

This is darn close, thank you for the help. What I don't understand is how this knows which manager is selected. I have the Manager as the slicer and when I select a manager I expect the SelectedManager variable to fill in with that manager, but I'm getting zero for all entries still. Will the new column know to reference the slicer automatically?

[lbendlin]

Power BI has concepts like "row context" and "filter context"  that allow it to figure out where in a visual the measure is and under which context it is being computed.

[keatonbeyer]

I have the formula almost working. It is a column on my table and it is always returning zero. I've tested different parts of it and it looks like the problem is the "SelectedManager" is coming back blank, despite having it in the slicer filter. I'm expecting it to come back with a managers name. Here's the column formula.

Show Project =

VAR SelectedManager = SELECTEDVALUE(Managers[Manager])

VAR ManagedUsers = FILTER(Managers,Managers[User Name]=SelectedManager)

VAR HasDirectOwnership = 'planview rpt_programs (3)'[tech_prog_mgr] IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])

VAR HasTaskOwnership =

COUNTROWS(

FILTER( 'planview rpt_raid','planview rpt_raid'[parent_task_key] = 'planview rpt_programs (3)'[primary_key] &&

'planview rpt_raid'[resource] IN SELECTCOLUMNS(ManagedUsers, "UserName", Managers[User Name])

)

) > 0

RETURN IF(HasDirectOwnership || HasTaskOwnership, 1, 0)

[lbendlin]

For such situations Power BI DAX has the EVALUATEANDLOG function and its companion app DAXDebugOutput  (or you can use SQL Server Profiler in a pinch).  They will help you find out where you go blank and why.

Note that SELECTEDVALUE requires exactly one value and will return blank otherwise.