Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Johan Vermeire]

Hi All,

I know that the PATH-function expects 2 columns (an Id and a Parent Id) and it returns the path from the top to the bottom, separated by the |-sign, as shown in the following screenshot

If the manager changes, we can simply update the data and it will still work.

But what if you have a ‘daily’ or ‘monthly’ view of your data. In that case, the formula returns an error

(In the following example, Person 1 left the company in February and person 2 become the top-manager

So I want that the PATH-function searches for the path, but taken into account the date.  And I can’t manage to do this.

It’s like the Path-function doesn’ use my filter. This is my formula:

Maybe it’s because DAX rescans the table for every level and that my filter is only working on the first level? But I don’t see how I would have to use then the earlier or earliest function. Or maybe another function?

Any one has an idea?
I’ve attached the example so you can play with it.

Thanks a lot,

Johan Vermeire

——————————
Johan Vermeire
IT Trainer
Xylos
——————————

[Sowmya Balakrishnan]

Hi Johan, 

This blog explains the workaround for the blanks in creating a hierarchy path:

https://www.wiseowl.co.uk/blog/s2479/parent-child-hierarchy.htm

Hope this helps you, to tactically remove the null entry from the PATH.

Thanks

——————————
Sowmya Balakrishnan
BI Consultant
——————————
——————————————-

[Sowmya Balakrishnan]

Another option – https://community.powerbi.com/t5/Desktop/Correcting-Errors-in-PATH/td-p/1428516

——————————
Sowmya Balakrishnan
BI Consultant
——————————
——————————————-

[Johan Vermeire]

Hi Sowmya,

First of all, thank you for taking the time to respond.
But the links that you post have nothing to do with this problem I think.

The blanks are not the problem.  The problem is that I cannot find the path to the parent as a user can have multiple parents.

But they have only 1 parent on a specific day.  So I’d like to find the path taken into account the date.  And it’s very easy to filter a table, but the PATH-function requires a column, not a table.  

Or did I overlook something?
Regards,
Johan

——————————
Johan Vermeire
IT Trainer
Xylos nv
0000000
——————————
——————————————-

[Noah Hadro]

yes I agree with you that the above information is not quite what I was looking for. Did you ever find a solution??

——————————
Noah Hadro
55068
——————————
——————————————-

[Johan Vermeire]

.

Sorry, I didn’t found a solution using the Path-function.

I have calculated it by creating a measure that works level by level?. Of course, this has 2 drawbacks:
– It’s slow
– It’s not ‘endless’.  I have calculated it for the 5 or 6 levels.

Maybe we should ask it ‘The Italians’ 🙂

——————————
Johan Vermeire
IT Trainer
Xylos nv
0000000
——————————
——————————————-