cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
systamper Member
Member

Need help with Hierarcy PathItem in Calculated Table

Hi, everyone.

 

I am working on data from Dynamics 365. The example below is a simplification. I have a Users table with their Manager (ReportsTo). I then have a Sales table. When a user logs into Power BI, I capture who they are and this passes through to a filter (represented as the slicer). I do NOT have a relationship between Sales and Users, since I am actually trying to capture information for all the people who report (either directly or indirectly) into a Manager. So for Sue, I should see sales for herself, for Lucy, and for Ziad. Whereas for Alice, I should see sales for all the users. If Bob logged in, he should only see himself.

 

2018-07-23_12-07-16.png

 

I am thinking the Path functionality is the best way to capture this. The steps I follow are

  1. Create a Path column in Users.
  2. Create a PathLookup column in Sales.
  3. Create a calculated table using the following code:
PathContains Sales =

FILTER (

    Sales,

    PATHCONTAINS ( Sales[PathLookup], SELECTEDVALUE ( Users[SalesPerson] ) )

)

 

 

 

However, this does not return any values. I am attaching the sample workbook. I feel like I am missing something obvious, so any and all help is most appreciated!

 

Best, Scott

1 REPLY 1
v-huizhn-msft Super Contributor
Super Contributor

Re: Need help with Hierarcy PathItem in Calculated Table

Hi @systamper,

PATHCONTAINS function is used to create a calculated column. Please create a calculated column in 'Sales' table and check if it is right.

 

Please refer to the following threads for more details.

Manager Level Hierarchies with multiples in Power BI
Creating a user hierarchy from a manager column to use as hierarchy slicer

Power BI Tutorial: Flatten Parent Child Hierarchy

Best Regards,
Angelia

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 317 members 3,278 guests
Please welcome our newest community members: