dax syntax - calculate(table) vs filter context transition and return confusion

[garythomannCoGC]

Dax query to emulate power bi dashboard report table results.  Hierarchical model of parent (pml) < child (mbm) < child (mbpl).

DaxStudio screenshot below.   "VAR pml_id" gives same error as shown.  " = 'pml'.[Id] "  link is a table so why the same error?

Code used above 

/*  model  pml < mbm < mbpl  */
DEFINE
    TABLE pml =    -- pml.id = 3921
        FILTER (
            'Project Master List',
            'Project Master List'[DIR] = "CO"  && 
            'Project Master List'[Initiative Name] = "Fleet Business Transformation"
        )

    VAR pml_id =   
        ROW ( 
            "Id", MAX ( 'pml'[Id] )
        )

    TABLE mbm = 
        FILTER (
            'Measure Benefit Master',
            'Measure Benefit Master'[DIR] = "CO" &&
            --'Measure Benefit Master'[PM IDId] = pml_id
            'Measure Benefit Master'[PM IDId] = 'pml'[Id]
        )

EVALUATE 
    VAR dummy = 'DirectorateAbbreviation'
RETURN 
    --dummy
    --pml
    --pml_id
    mbm

[garythomannCoGC]

Posting the final working solution(s) for the parent < child model

Run with only mbm changed as per suggestion from Owen

Run with suggested changes to both var queries

[garythomannCoGC]

Error pointing to need for table usage, there are 4 rows to be connected.

Try as table declarations and same error

> one can not have nested references   Introducing DEFINE TABLE in DAX queries

> now I understand the preference for variable  @OwenAuger 

reducing the query further      Defining variables in DAX queries

[OwenAuger · Accepted solution]

Yes, you've hit the nail on the head: "query tables" created with DEFINE TABLE cannot reference each other!

But variables can 🙂

I'm not sure if your query has evolved since your last post, but I would possibly suggest something like:

EVALUATE
VAR pml_id =
    -- pml.id = 3921
    CALCULATE (
        MAX ( 'Project Master List'[Id] ),
        'Project Master List'[DIR] = "CO",
        'Project Master List'[Initiative Name] = "Fleet Business Transformation"
    )
VAR mbm =
    CALCULATETABLE (
        'Measure Benefit Master',
        'Measure Benefit Master'[DIR] = "CO",
        'Measure Benefit Master'[PM IDId] = pml_id
    )
RETURN
    mbm

[garythomannCoGC]

Interim question :}     DAX why the error when the focus has changed

[OwenAuger]

Hi @garythomannCoGC 

I would suggest doing away with the DEFINE TABLE statements, and just use DEFINE VAR.

I couldn't see anything in the query that relied on query tables, so table variables should be sufficient.

Using this method, pml_id can be defined using MAXX.

Does the below work?

/*  model  pml < mbm < mbpl  */
DEFINE
    VAR pml =
        -- pml.id = 3921
        FILTER (
            'Project Master List',
            'Project Master List'[DIR] = "CO"
                && 'Project Master List'[Initiative Name] = "Fleet Business Transformation"
        )
    VAR pml_id =
        MAXX ( pml, 'Product'[Id] )
    VAR mbm =
        FILTER (
            'Measure Benefit Master',
            'Measure Benefit Master'[DIR] = "CO"
                && 'Measure Benefit Master'[PM IDId] = pml_id
        )

EVALUATE
VAR dummy = 'DirectorateAbbreviation'
RETURN --dummy
    --pml
    --pml_id
    mbm

 Regards

[garythomannCoGC]

@OwenAuger Thank you.   Lots of reading via searching 'dax calculatetable vs filter return value'.

FILTER vs CALCULATETABLE  was a good discussion around the difference.  'CALCULATETABLE triggers context transition whereas FILTER does not.  Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not.'  Yet when you look at their defininitions both return tables :}