Forum Discussion

keatonbeyer's avatar
keatonbeyer
Frequent Visitor
9 months ago
Solved

Dynamic filter using lookups

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 owner...
Need Help
  • Rufyda's avatar
    Rufyda
    9 months ago

    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

Rufyda's avatar
Rufyda
Super User
9 months ago

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's avatar
keatonbeyer
Frequent Visitor
9 months ago
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's avatar
    Rufyda
    Super User
    9 months ago

    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's avatar
    lbendlin
    Super User
    9 months ago

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

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