Tonight I wanted to test some of the hierarchy functions in DAX. But after some testing I got very frustrated…
I have this sample DAX query
EVALUATE
FILTER (
SUMMARIZE (
‘Product SubCategory’,
‘Product SubCategory'[Product SubCategory Name],
“Sales”, ‘Internet Sales'[Internet Total Sales],
“Path”, PATH (
‘Product SubCategory'[Product Subcategory Id],
‘Product SubCategory'[Product Category Id]
),
“PathItem”, PATHITEM (
PATH (
‘Product SubCategory'[Product Subcategory Id],
‘Product SubCategory'[Product Category Id]
),
2,
1
),
“PathItemReverse”, PATHITEMREVERSE (
PATH (
‘Product SubCategory'[Product Subcategory Id],
‘Product SubCategory'[Product Category Id]
),
2,
1
),
“Product Category Name”, LOOKUPVALUE (
‘Product Category'[Product Category Name],
‘Product Category'[Product Category Id], PATHITEMREVERSE (
PATH (
‘Product SubCategory'[Product Subcategory Id],
‘Product SubCategory'[Product Category Id]
),
2,
1
)
)
),
‘Internet Sales'[Internet Total Sales] <> 0
)
ORDER BY “Value PathItem” ASC
This produces this output
Except for the ordering this looks almost correct. But one row looks strange. The “Mountain Bikes” does not have any path at all.
If you pivot the same thing in Excel you will get this result.
Can anyone please help me with this problem?
A blog about Analytics, AWS and Azure 
Hi Sindre!
I do believe PATH was constructed with tables with true internal hierarchies in mind, where only the top node(s) has the same ID and ParentID. In the Product Subcategory you find that ID=1 and ParentID=1 for Mountain Bikes. MDX from Excel handles it, DAX does it too if you create a normal hierarchy in PowerPivot, for example. The PATH function struggles in this case, however.
Hi John Olav!
Thanks for the comment. I was suspecting something like that. I did test the function on a hierarchy in the date dimension too. But unfortunately it looked like it only worked with integers.
Guess I have to make myself a real hierarchy to test on 🙂
Not sure if I am to happy on how DAX handles hierarchies. At least if you compare to MDX.
Yeah, try a real p/c hierarchy table 🙂 PATH works only on integer and text IDs, btw.
And I do agree, hierarchy handling in DAX is definitely not a reason to choose Tabular. But there are so many other advantages that we accept this shortcoming for now.