Forum Discussion
Dynamic filter using lookups
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
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
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?
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.
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]))) > 0RETURN IF(HasDirectOwnership || HasTaskOwnership, 1, 0)
- 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])))>0RETURN IF(HasTaskOwnership|| HasDirectOwnership , 1, 0)
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.Remember that your FILTER is applied on top of the current filter context. Better use
VAR ManagedUsers = FILTER(ALL(Managers),[Manager]=SelectedManager)
