Hierarchy testing in DAX (attempt number 2)

Here is some new query samples on how DAX hierarchies work. And this time it will work ūüôā

Id did a little modification of the model. I had to make a new hierachy in the employee table.

Hierachy in tabular model

Then you will have to deploy the model to the server.

My sample query looks like this:

DEFINE
    MEASURE 'Employee'[Employeelevel] =
        PATHLENGTH (
            PATH ( Employee[EmployeeKey], Employee[ParentEmployeeKey] )
        )
    MEASURE 'Employee'[ParentEmployee] =
        LOOKUPVALUE (
            'Employee'[Last Name],
            'Employee'[EmployeeKey], PATHITEMREVERSE (
                PATH (
                    'Employee'[EmployeeKey],
                    'Employee'[ParentEmployeeKey]
                ),
                2,
                1
            )
        )
    MEASURE 'Employee'[PathReverse] =
        PATHITEMREVERSE (
            PATH (
                'Employee'[EmployeeKey],
                'Employee'[ParentEmployeeKey]
            ),
            2,
            1
        )
    MEASURE 'Employee'[Path] =
        PATH (
            'Employee'[EmployeeKey],
            'Employee'[ParentEmployeeKey]
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Employee',
        'Employee'[EmployeeKey],
        'Employee'[Last Name],
        "Sales", 'Reseller Sales'[Reseller Total Sales],
        "Path", 'Employee'[Path],
        "PathItemReverse", 'Employee'[PathReverse],
        "Employeelevel", 'Employee'[Employeelevel],
        "ParentEmployee", 'Employee'[ParentEmployee]
    ),
    ISBLANK ( 'Reseller Sales'[Reseller Total Sales] ) = FALSE
)
ORDER BY 'Employee'[ParentEmployee], 'Employee'[EmployeeKey]

And the result looks like this

hierarchy correct

To verify the result I made an Excel pivot.

Advertisements

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?