Testing some hierarchy stuff in DAX

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

Hierarchy DAX

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.

Hierarchy Excel

Can anyone please help me with this problem?

3 thoughts on “Testing some hierarchy stuff in DAX

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s