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

Define a measure. Make your code more readable

To make your code more readable you can create measures outside the evaluate statement. Let’s see an example on how we can do this.

Take a look at the query that returns sales per year versus sales last year.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", CALCULATE(SUM ( 'Internet Sales'[Sales Amount] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] ))
),
[Sales last year] <> 0
)

To move the calculation for “Sales last year” you can define a measure outside the evaluate statement like below. This can be very useful if you have lots of logic inside your query.

DEFINE
MEASURE 'Internet Sales'[Sales last year] =
CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ))

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", 'Internet Sales'[Sales last year]
),
[Sales last year] <> 0
)

If you want more than one measure you just add new measures below the first one. But the “DEFINE” should just written once at the top.