dax hierarchical var table calls - parent < child < child model

[garythomannCoGC]

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

Solved the  parent < child  part.  Now after extensive searching, how to integrate a tiertiary child call.

Feel like I'm back to the same situation as previous post but different error :} 

/*  model  pml < mbm < mbpl  */
EVALUATE 
    VAR pml_id =    -- pml.id = 3921,  pml.id (string)
        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" &&    -- no data integrity :}
            'Measure Benefit Master'[PM IDId] = pml_id   -- both keys are string, 3921
        )

    VAR mbpl = 
        CALCULATETABLE (
            'Measure Benefit Progress List',
            'Measure Benefit Progress List'[Year Reported] = "2021-22"  &&
            'Measure Benefit Progress List'[Main ID LkUpId] = mbm[Id]   -- both keys are string, 439 440 441 442
        )
/*
---  main query  ---
    VAR mbpl_columns = 
        SELECTCOLUMNS (
            'mbpl', 
            "Annual Status",                    'Measure Benefit Progress List'[Annual Status],
            "Annual Status Score",              'Measure Benefit Progress List'[Annual Status Score],
            "Annual Status Traffic Light",      'Measure Benefit Progress List'[Annual Status Traffic Light]
        )
*/
RETURN 
    --ROW ( "pml_id", pml_id )   -- its a return statement so a table of sorts must be returned
    --mbm
    mbpl
    --mbpl_columns

Cannot find table 'mbm' error, but the mbm var declared table is there!?

[garythomannCoGC]

And 'cool o rama' the next bit works as intended with out further change ...

/*  model  pml < mbm < mbpl  */
EVALUATE 
    VAR pml_id =   -- pml.id = 3921,  pml.id (string)
        CALCULATE (
            MAX ( 'Project Master List'[Id] ),
            'Project Master List'[DIR] = "CO"  && 
            'Project Master List'[Initiative Name] = "Fleet Business Transformation"
        )

    VAR mbm_id_values = 
        CALCULATETABLE (
            VALUES ( 'Measure Benefit Master'[Id] ), -- handle single or multiple Id values
            --'Measure Benefit Master'[DIR] = "CO" &&    -- no data integrity :}
            'Measure Benefit Master'[PM IDId] = pml_id   -- both keys are string, 3921
        )

    VAR mbpl =   -- mbm.Id == 439 440 441 442
        CALCULATETABLE (
            'Measure Benefit Progress List',
            'Measure Benefit Progress List'[Year Reported] = "2021-22"  &&
            'Measure Benefit Progress List'[Main ID LkUpId] IN mbm_id_values   -- both keys are string, 439 440 441 442
        )

    VAR mbpl_columns = 
        SELECTCOLUMNS (
            'mbpl', 
            "Annual Status",                    'Measure Benefit Progress List'[Annual Status],
            "Annual Status Score",              'Measure Benefit Progress List'[Annual Status Score],
            "Annual Status Traffic Light",      'Measure Benefit Progress List'[Annual Status Traffic Light]
        )

RETURN 
    --ROW ( "pml_id", pml_id )   -- its a return statement so a table of sorts must be returned
    --mbm
    --mbpl
    mbpl_columns

DaxStudio output

[garythomannCoGC]

@OwenAuger sorry to be a pain but thought to dm as helped previously.  I must be doing something brain dead in dax world but still can not figure this out :}

[OwenAuger · Accepted solution]

Hi @garythomannCoGC 

Looking at the code, the immediate cause of the error is that you can't reference a column of a table variable by 'Table Variable Name'[Column Name].

Below is a suggested change with mbm_id_values which retrieves the distinct values of 'Member Benefit Master'[Id]. These are then applied as a filter in mbpl.

Does this work?

/*  model  pml < mbm < mbpl  */
EVALUATE 
    VAR pml_id =    -- pml.id = 3921,  pml.id (string)
        CALCULATE (
            MAX ( 'Project Master List'[Id] ),
            'Project Master List'[DIR] = "CO"  && 
            'Project Master List'[Initiative Name] = "Fleet Business Transformation"
        )

    VAR mbm_id_values = 
        CALCULATETABLE (
            VALUES ( 'Measure Benefit Master'[Id] ), -- handle single or multiple Id values
            --'Measure Benefit Master'[DIR] = "CO" &&    -- no data integrity :}
            'Measure Benefit Master'[PM IDId] = pml_id   -- both keys are string, 3921
        )

    VAR mbpl = 
        CALCULATETABLE (
            'Measure Benefit Progress List',
            'Measure Benefit Progress List'[Year Reported] = "2021-22"  &&
            'Measure Benefit Progress List'[Main ID LkUpId] IN mbm_id_values -- both keys are string, 439 440 441 442
        )
/*
---  main query  ---
    VAR mbpl_columns = 
        SELECTCOLUMNS (
            'mbpl', 
            "Annual Status",                    'Measure Benefit Progress List'[Annual Status],
            "Annual Status Score",              'Measure Benefit Progress List'[Annual Status Score],
            "Annual Status Traffic Light",      'Measure Benefit Progress List'[Annual Status Traffic Light]
        )
*/
RETURN 
    --ROW ( "pml_id", pml_id )   -- its a return statement so a table of sorts must be returned
    --mbm
    mbpl
    --mbpl_columns

[garythomannCoGC]

@OwenAuger thank you yes it does.  

Now I understand, that I needed to setup the array for mbm explicitly.  I did try using 'IN' like an sql in-list but ... :}

It's funny 'the powers that be' are happy to send folks on training courses but when it comes to IT they think we do it all by magic!  I have to revisit the sqlbi.com course I bought and hardly had time for over a year ago.  Always hitting things on an as needed basis :}

Thanks once again for your help.

[garythomannCoGC]

DaxStudio output of this run for context for others

[garythomannCoGC]

Power BI dataset model portion  pml < mbm < mbpl  parent child child hierarchy.

pml < mbm  joins

mbm < mbpl