topic Re: dax syntax - calculate(table) vs filter context transition and return confusion in DAX Commands and Tips

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

garythomannCoGC — Fri, 18 Aug 2023 05:29:13 GMT

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

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

OwenAuger — Wed, 16 Aug 2023 10:16:49 GMT

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

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

garythomannCoGC — Thu, 17 Aug 2023 00:13:56 GMT

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

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

garythomannCoGC — Thu, 17 Aug 2023 06:00:40 GMT

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

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

OwenAuger — Thu, 17 Aug 2023 12:02:17 GMT

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

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

garythomannCoGC — Fri, 18 Aug 2023 02:16:08 GMT

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

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

garythomannCoGC — Fri, 18 Aug 2023 05:11:23 GMT

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