topic dax hierarchical var table calls - parent < child < child model in DAX Commands and Tips

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

garythomannCoGC — Tue, 22 Aug 2023 06:07:23 GMT

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!?

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

garythomannCoGC — Tue, 22 Aug 2023 05:33:47 GMT

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

pml < mbm joins

mbm < mbpl

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

garythomannCoGC — Wed, 23 Aug 2023 03:18:14 GMT

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 :}

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

OwenAuger — Fri, 25 Aug 2023 00:26:12 GMT

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

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

garythomannCoGC — Fri, 25 Aug 2023 04:38:20 GMT

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.

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

garythomannCoGC — Fri, 25 Aug 2023 04:34:49 GMT

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

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

garythomannCoGC — Fri, 25 Aug 2023 04:36:14 GMT

DaxStudio output of this run for context for others