Forum Discussion

Sharmilshah's avatar
Sharmilshah
Frequent Visitor
1 year ago
Solved

Filter data based on Hierarchies

Hello All,

 

On my powerbi dashboard, I am looking for creating a slicer/button which has 3 options to filter a visual: 
1) My work item

2) My directs

3) Entire org

 

I tried different ways by creating a measure and using it in slicer but it didn't work. Tested below ways:

  • Fetched hierarchy into semantic model and created a path column.
  • With another measure which shows if logged in user has direct report or not. If it is 1 then it filter based on that. This works when I have 1 direct report but in case of multiple direct reports, it is not working.
  • Also tried to filter with power automate but not sure how to pass the variable etc.

    Any guidance would be helpful.
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Sharmilshah 

    Thanks for Sahir_Maharaj's reply. You can refer to his method. If this still doesn't solve your problem, you can share the pbix file without sensitive data and the results expected to achieve for testing.

    Best Regards,
    Yang
    Community Support Team

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello Sharmilshah,

     

    Can you please try this approach:

    HierarchyFilterMeasure = 
VAR SelectedOption = SELECTEDVALUE('HierarchyFilter'[FilterType])
VAR CurrentUser = USERPRINCIPALNAME()
VAR DirectReports = CALCULATETABLE(
    VALUES(WorkItems[Owner]), 
    WorkItems[Manager] = CurrentUser
)

RETURN 
SWITCH(
    TRUE(),
    SelectedOption = "My Work Item", IF(MAX(WorkItems[Owner]) = CurrentUser, 1, BLANK()),
    SelectedOption = "My Directs", IF(MAX(WorkItems[Owner]) IN DirectReports, 1, BLANK()),
    SelectedOption = "Entire Org", 1,
    BLANK()
)

    You can implement this by creating a new table for the slicer:

    HierarchyFilter = 
DATATABLE("FilterType", STRING, 
{
    {"My Work Item"},
    {"My Directs"},
    {"Entire Org"}
})

    Then, set the visual-level filter:

    HierarchyFilterMeasure <> BLANK()

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Sharmilshah 

    Thanks for Sahir_Maharaj's reply. You can refer to his method. If this still doesn't solve your problem, you can share the pbix file without sensitive data and the results expected to achieve for testing.

    Best Regards,
    Yang
    Community Support Team