Path not getting resolved - need to understand why!

[philippfrenzel]

Hi,

I try to build a PATH based upon a parent child hierachy wich isn't totally clean. After a couple of fixes, I'm currently stuck here:

Values which should build the path

The error says I have multiple value for 045444 but I don't see where... Filter 1) if set on MasterItem.

And here is the result for filter on child id:

So I understand, that the function has an issue with the fact, that the child can appear in multiple masters... how should this be solved as best practice?

Thanks in advance Philipp

[ElenaN · Accepted solution]

Hello,

The Why part is explained in the definition of Path function:

"If ID_columnName has duplicates and parent_columnName is the same for those duplicates then PATH() returns the common parent_columnName value; however, if parent_columnNamevalue is different for those duplicates then PATH() returns an error. In other words, if you have two listings for the same employee ID and they have the same manager ID, the PATH function returns the ID for that manager. However, if there are two identical employee IDs that have different manager IDs, the PATH function returns an error." 

Extract taken from the definition of the function:

https://docs.microsoft.com/en-us/dax/path-function-dax

Some of the ways to solve this would be:

- ImkeF's blog posts (Power Query):

https://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi...

https://www.thebiccountant.com/2017/06/13/bill-of-material-bom-explosion-part2-costing-in-excel-and-...

https://www.thebiccountant.com/2017/06/18/bill-of-materials-implosion-or-where-used-analysis-in-powe...

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...

- DAX:

https://powerpivotpro.com/2018/09/using-dax-to-handle-multiple-parent-hierarchies/

Regards,

ElenaN

[philippfrenzel]

Hi ElenaN, 

thanks for your support, I'll check the links which seem to be promising and let you know later if it works out 😉

Cheers Philipp