Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
garythomannCoGC
Impactful Individual
Impactful Individual

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.  " = 'pml'.[Id] "  link is a table so why the same error?

 

garythomannCoGC_0-1692168788020.png

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

 

 

 

 

 

1 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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

6 REPLIES 6
garythomannCoGC
Impactful Individual
Impactful Individual

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

 

Run with only mbm changed as per suggestion from Owen

garythomannCoGC_0-1692335325333.png

 

Run with suggested changes to both var queries

garythomannCoGC_1-1692335446326.png

 

garythomannCoGC
Impactful Individual
Impactful Individual

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

garythomannCoGC_0-1692243261171.png

Try as table declarations and same error

garythomannCoGC_1-1692243882316.png

> 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

garythomannCoGC_0-1692251922027.png

 

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn
garythomannCoGC
Impactful Individual
Impactful Individual

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

 

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.