Forum Discussion

garythomannCoGC's avatar
garythomannCoGC
Impactful Individual
3 years ago
Solved

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

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....
"Help"
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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
OwenAuger's avatar
OwenAuger
Super User
3 years ago

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's avatar
    garythomannCoGC
    Impactful Individual
    3 years ago

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