DAX STUDIO - Using a column var result as input on Evaluate Function

Question

In DAX Studio I'm evaluation the next code:

EVALUATE
ADDCOLUMNS  (
            'Parent Child',
            "Path",PATH('Parent Child'[EmployeeKey],'Parent Child'[ParentEmployeeKey]),
            "Path Length", PATHLENGTH(PATH('Parent Child'[EmployeeKey],'Parent Child'[ParentEmployeeKey])),
            "Path Item", PATHITEM(PATH('Parent Child'[EmployeeKey],'Parent Child'[ParentEmployeeKey]), 3)
            )

I'm getting the right result. However, I wonder if I can implement something like this:

DEFINE 
VAR p = PATH('Parent Child'[EmployeeKey],'Parent Child'[ParentEmployeeKey])
EVALUATE
ADDCOLUMNS  (
            'Parent Child',
            "Path",p,
            "Path Length", PATHLENGTH(p),
            "Path Item", PATHITEM(p, 3)
            )

I've tried some answers provided on different forums, but so far I'm not getting any possible solution.

Regards,

Have you tested my suggestion? Have you succeed with your problem? — Przemyslaw Remin, Feb 07 '20 at 10:05

Przemyslaw Remin · Answer 1 · 2020-02-04T09:02:37.477

DEFINE does not go that deep in its scope as you want.

https://www.sqlbi.com/articles/defining-variables-in-dax-queries/

When the definition of a variable follows the EVALUATE statement its scope is only the table expression referenced by the EVALUATE statement.

You can define a variable in each EVALUATE statement. I do not have your sample data, then I cannot test it:

EVALUATE
VAR p = PATH('Parent Child'[EmployeeKey],'Parent Child'[ParentEmployeeKey])
RETURN
ADDCOLUMNS  (
            'Parent Child',
            "Path",p,
            "Path Length", PATHLENGTH(p),
            "Path Item", PATHITEM(p, 3)
            )

Alternatively try wrapping ADDCOLUMNS with CALCULATETABLE:

https://blog.learningtree.com/excel-2016-dax-variables/

DEFINE
    VAR p =
        PATH ( 'Parent Child'[EmployeeKey], 'Parent Child'[ParentEmployeeKey] )
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        'Parent Child',
        "Path", p,
        "Path Length", PATHLENGTH ( p ),
        "Path Item", PATHITEM ( p, 3 )
    )
)

DAX STUDIO - Using a column var result as input on Evaluate Function

1 Answers1