Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
There are two requirements to PATH:
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]
)
)
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):
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:
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
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
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!
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?
Hi,
Yes parent_id only contains blanks and values that exisit in item_id, and both datatypes are text data types.
Mike
Hi
Just wondering if anyone can help with this?
Thanks
Mike
Same exact thing is happening for me. Did you resolve it?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |