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.
There are several posts on dealing with errors that occur when using the PATH function in DAX. All of the ones I've seen focus on fixing the data before trying to use PATH. In my case, I'm using the path to do some LOOKUPVALUE calls to determine other values. In short, if the PATH fails to find a path for any reason, that's ok. Given some recent changes in the data, I now get the following error:
The value '1349021' in 'MyTable'[ParentId] must also exist in 'MyTable'[Id]. Please add the missing data and try again.
So I tried the following code to ignore the error:
Parent Path = IFERROR ( PATH ( [Id], [ParentId] ), "" )
My assumption was that IFERROR () would trap the error and allow me to proceed with a blank value. Why can't errors in the PATH function actually be trapped and handled? Any help would be appreciated.
Solved! Go to Solution.
Thank you for the information @v-shex-msft . The problem needs to be solved in two steps. Step one is to make a Parent Search Id using LOOKUPVALUE, as follows:
ParentSearchValue = LOOKUPVALUE ( [Id], [Id], [ParentId] )
The LOOKUPVALUE function handles the failures. Step two is to proceed with determining the PATH, using ParentSearchValue instead of ParentId.
Hi @gaccardo-slb ,
I think path function analytic on whole field records instead each row. So if these records are not stored as parent child hierarchy, it will return error due to multiple available relationship mapping.
In my opinion, you can add a calculated column to replace error records and use this column in path function.
Regards,
Xiaoxin Sheng
Thank you for the information @v-shex-msft . The problem needs to be solved in two steps. Step one is to make a Parent Search Id using LOOKUPVALUE, as follows:
ParentSearchValue = LOOKUPVALUE ( [Id], [Id], [ParentId] )
The LOOKUPVALUE function handles the failures. Step two is to proceed with determining the PATH, using ParentSearchValue instead of ParentId.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |