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?