Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Path() Error

Hi all,

 

I'm running into problems with the PATH formula in PBI desktop.

 

I have a table of items [item_id] and parents [parent_id], and I'm trying to create a calculated column to show the path

=PATH([item_id],[parent_id]).

 

I keep getting the following error:

 

The value '' in Table1[parent_id] must also exist in Table1[item_id]. Please add the missing data and try again.

 

There is always going to be an item with no parent (top of the hierarchy) so I'm not sure why I am getting this error.

 

I have also tried this in powerpivot in excel and it works, so looks to be a PBI desktop issue.

 

Can anyone help? I've searched on the forum and found a couple of similar threads but no answers.

 

Regards

 

Mike

9 REPLIES 9
TimoRiikonen
Advocate III
Advocate III

There are two requirements to PATH:

  • You must have all parents defined as children
  • You can't have more than one parent for each child

 

Fix to second one is that you don't use PATH at all, but break up the structure in PowerQuery or DAX (the latter was too slow for me).

Fix example for the first case:


-- Create missing parents for PATH command
Missing assemblies =
CALCULATETABLE(
SUMMARIZE(
'Item',
'Item'[Item id],
'Item'[Description],
'Item'[Inventory],
"Assembly id", BLANK(),
"Can ship", 1,
"Quantity per", 1
),
FILTER( 'Item', NOT( 'Item'[Item id] IN ALL( 'BOM Component'[Component id] )))
)
Hierachical assemblies =
UNION(
SELECTCOLUMNS(
'BOM Component',
"Assembly id", 'BOM Component'[Assembly id],
"Can ship", 'BOM Component'[Can ship],
"Component description", 'BOM Component'[Component description],
"Component id", 'BOM Component'[Component id],
"Inventory", 'BOM Component'[Inventory],
"Quantity per", 'BOM Component'[Quantity per]
),
SELECTCOLUMNS(
'Missing assemblies',
"Assembly id", 'Missing assemblies'[Assembly id],
"Can ship", 'Missing assemblies'[Can ship],
"Component description", 'Missing assemblies'[Description],
"Component id", 'Missing assemblies'[Item id],
"Inventory", 'Missing assemblies'[Inventory],
"Quantity per", 'Missing assemblies'[Quantity per]
)
)
kanadc
New Member

My 2 cents here; hope it helps someone.

I was facing the same error; my data is in .xlsx file. So you will possibly have to try a few options before this works.

 

So, my data looked like this (all Columns as "Text", please ignore the Dates):

  

Original Data.JPG

I had only 1 root node and power BI was throwing the error for the PARENTID for this node.

So, what I did is to just Delete the PARENTID field and made it look like this:

 

Modified Data.JPG

Once I did that, Power BI started interpreting the PARENTID as "NULL". And the error went away.

So my guess is, it's using the NULL in the Parent_Key as a termination criteria for the PATH( ) function. And I'm guessing if you replace the Parent_Key field for all your root nodes with NULLS, it will probably work for you.

 

The original source of my data is a SAP HANA view. Hopefully I will be able to figure out how to get it working directly from HANA and not off my Excel solution here Smiley Happy

In my case the records at the top of the hierarchy already have a NULL value for the parentID.  What is strange is that PowerBI seems to not be seeing them as blank.  When I do a new column as TEST = ISBLANK(parentID), I get false on all records even those that have no value in that column.  I suspect that is the root of the error bc. if PowerBI doesn't see it as a blank, then it would be complaining that it can't find that parentID in the recordID column when doing PATH = PATH(recordID, parentID)

I was able to fix the underlying issue by using the replace values function

2018-10-08_11-15-32.png

Thanks guys! I was having the same issue! using the replace values I replaced the top hierarchy "Blank" with "null" and the error has gone! 

 

Thanks again people! Smiley Very Happy

sdjensen
Solution Sage
Solution Sage

Can you verify that you don't have a value in parent_id that doesn't exists as an item_id besides the blanks.

 

Can you verify that item_id and parent_id is the same datatype?

/sdjensen
Anonymous
Not applicable

Hi,

Yes parent_id only contains blanks and values that exisit in item_id, and both datatypes are text data types.

 

Mike

Anonymous
Not applicable

Hi

 

Just wondering if anyone can help with this?

 

Thanks

 

Mike

Same exact thing is happening for me.  Did you resolve it?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.