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

keatonbeyer's avatar
keatonbeyer
Frequent Visitor
9 months ago

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

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

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

      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.