How to make Slicer Work on Inactive Relationship in PowerBI?

Question

I’m working on a Power BI model with the following relationships:

DimManager is connected to DimProjects through a direct relationship (via ManagerID). DimProjects contains project-level details and is connected to DimTasks through ProjectID. DimTasks contains task-level details, including TaskID and ProjectID.

Problem: When I add a slicer for ManagerName from DimManager, it filters the DimProjects table as expected. However, it does not propagate to the DimTasks table, meaning that the tasks shown are not filtered by the selected manager.

Relationships in the Model: DimManager → DimProjects (Active, via ManagerID). DimProjects → DimTasks (Inactive, via ProjectID).

Expected Behavior: If I select "Alice" in the slicer, only Task 1 (associated with Project A) should appear in the tasks table.

Question:

Why does the filter on ManagerName fail to propagate to the DimTasks table?
How can I fix this so that filtering on ManagerName in the slicer filters the tasks table correctly?

Dimensions should never be connected to each other; only to fact tables. You need to change your data model (hint: tasks should be a fact table, not a dimension). — RADO
– RADO, Commented Nov 19, 2024 at 7:49
My Fact Table is the Timesheet, which only contains the transactional records, Is it a good idea to add task details to fact table? — Meet Bardoliya
– Meet Bardoliya, Commented Nov 19, 2024 at 14:46

Ryan · Accepted Answer · 2024-11-19 00:45:24Z

0

I think the problem is the relationship between project and task tables are inactive. Please try to change the relationship to active.

or you can try merge tables in PQ to get all data in one table

let
    Source = Table.NestedJoin(Manager, {"ManagerID"}, Project, {"ManagerID"}, "Project", JoinKind.LeftOuter),
    #"Expanded Project" = Table.ExpandTableColumn(Source, "Project", {"ProjectID", "ProjectName"}, {"ProjectID", "ProjectName"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Project", {"ProjectID"}, Tasks, {"ProjectID"}, "Tasks", JoinKind.LeftOuter),
    #"Expanded Tasks" = Table.ExpandTableColumn(#"Merged Queries", "Tasks", {"TaskID", "TaskName"}, {"TaskID", "TaskName"})
in
    #"Expanded Tasks"

or you can use DAX to get the data from task table

taskID =
MAXX (
    FILTER ( Tasks, Tasks[ProjectID] = MAX ( Project[ProjectID] ) ),
    Tasks[TaskID]
)

edited Nov 19, 2024 at 0:45

answered Nov 19, 2024 at 0:36

Ryan

2,6891 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make Slicer Work on Inactive Relationship in PowerBI?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related