Simple DAX query with parent child relationship

[garythomannCoGC]

Summary

    RELATED join not working for simple parent child dax query

Details

+  simple parent child table relationship in model 

. screenshot, model (1)

-  Measure Benefit Master (MBM) is the parent

.   MBM.Id  is defined as Text but all integer values

-  Measure Benefit Progress List (MBPL) is the child of MBM

.   MBPL.Main ID LkUpId  is defined as Text but all integer values  

+  data test

-  MBM.ID = 148

.  MBM  screenshot, dataset data (2)

.  MBPL screenshot, dataset data (3)

- our query with hardcoded test value, works and returns correct MBPL.Checkpoint value for the next financial year 2023-24

. query  (1)

>  this works

+  query that should work when added to dataset

. query  (2)

Screenshots

model (1)

dataset data (2)

dataset data (3)

Querys & DaxStudio results

query (1)

--CheckPoint Next Year = 
/*  Summary     routine to find the checkpoint value for the next financial year 
    psuedo Code
    +  find current financial year 
    .  drive off first 4 characters of our financial year format
    -  find the next financial year
    +  find the checkpoint value for next financial year
    History     042023 gt  build
 */

EVALUATE
VAR THISYEAR  = YEAR  ( TODAY() ) 
VAR THISMONTH = MONTH ( TODAY() ) 
VAR FINANCIALYEAR = IF ( THISMONTH <= 6, THISYEAR - 1 )  
VAR FINANCIALYEARNEXT = FINANCIALYEAR + 1 
VAR CHECKPOINTNEXTYEAR = 
    CALCULATE (
        VALUES (
            'Measure Benefit Progress List'[CheckPoint]  
        ),
        VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARNEXT ) && 
        'Measure Benefit Progress List'[Main ID LkUpId] = "148"
        ---RELATED ( 'Measure Benefit Progress List'[Main ID LkUpId] ) = 'Measure Benefit Master'[Id]   -- this should work
        ---'Measure Benefit Progress List'[Main ID LkUpId] = RELATED ( 'Measure Benefit Master'[Id] )
/*
        'Measure Benefit Progress List'[Main ID LkUpId] = 
            LOOKUPVALUE ( 
                'Measure Benefit Master'[Id],     -- get value  
                'Measure Benefit Master'[Id],     -- search in 
                'Measure Benefit Progress List'[Main ID LkUpId]    -- search for 
            ) 
*/
    ) 
RETURN 
    { CHECKPOINTNEXTYEAR }

daxstudio result

query (2)

--CheckPoint Next Year = 
/*  Summary     routine to find the checkpoint value for the next financial year 
    psuedo Code
    +  find current financial year 
    .  drive off first 4 characters of our financial year format
    -  find the next financial year
    +  find the checkpoint value for next financial year
    History     042023 gt  build
 */

EVALUATE
VAR THISYEAR  = YEAR  ( TODAY() ) 
VAR THISMONTH = MONTH ( TODAY() ) 
VAR FINANCIALYEAR = IF ( THISMONTH <= 6, THISYEAR - 1 )  
VAR FINANCIALYEARNEXT = FINANCIALYEAR + 1 
VAR CHECKPOINTNEXTYEAR = 
    CALCULATE (
        VALUES (
            'Measure Benefit Progress List'[CheckPoint]  
        ),
        VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARNEXT ) && 
        ---'Measure Benefit Progress List'[Main ID LkUpId] = "148"
        RELATED ( 'Measure Benefit Progress List'[Main ID LkUpId] ) = 'Measure Benefit Master'[Id]   -- this should work
        ---'Measure Benefit Progress List'[Main ID LkUpId] = RELATED ( 'Measure Benefit Master'[Id] )
/*
        'Measure Benefit Progress List'[Main ID LkUpId] = 
            LOOKUPVALUE ( 
                'Measure Benefit Master'[Id],     -- get value  
                'Measure Benefit Master'[Id],     -- search in 
                'Measure Benefit Progress List'[Main ID LkUpId]    -- search for 
            ) 
*/
    ) 
RETURN 
    { CHECKPOINTNEXTYEAR }

daxstudio run result error

Notes

+ MBPL.Financial Year Search 

-  is a simple cutdown of the first 4 character portion of MBPL.Financial Year within the dataset

> used in the cross reference match within the query

Both MBM.Id  and  MBPL.Main ID LkUpId  are defined as Text but contain integer values only so could be converted to integer data type within the dataset model.  But I read somewhere that doing so would not make much difference to speed etc.  Should I go ahead and convert the PK columns to integer data type?

I am aware that RELATED wont work within CALCULATE.  One question is how to build the query so it runs in both daxstudio /dataset with a test value and then works within daxstudio/dataset with the test line removed.

Also aware that my thinking is sql based.  One value test CALCULATE makes sense as row based.  What should be the approach for the dataset column?  The first line of the query   CheckPoint Next Year =   would be uncommented to create the new column in MBPL table.

[garythomannCoGC]

How to rewrite summarize with addcolumns for my scenario 

And for completeness a link to the above further question and how the querys can be further improved.

Thanks for your help.  Much appreciated :}

[garythomannCoGC · Accepted solution]

Finally figured out my confusion.  The following currently works within my limited DAX knowledge.

So in short yes from our parent table MBM we can setup a column to access specific column row data for a child table MBPL.

New table
x Reference Dates as mentioned in previous Constant values email

New calculated columns
MBM.CheckPoint Next the value of MBPL.CheckPoint for the next financial year
MBM.CheckPoint Target the value of MBPL.CheckPoint for the target financial year

Coding notes
To help our understanding and work to a development method I have kept the testing code lines. These code lines are needed to make the DAX code work in daxstudio during development. And are commented out when added to the EPM dataset.

--x Reference Dates =   -- uncomment for pbi desktop 
/*  Summary     table of constant date values
    History     042023 gt  build
    Notes       reference tables separated by data type rather than one giant table
                reference table naming  _Reference <data type> 
 */
EVALUATE   -- comment out for pbi desktop but needed for daxstudio
    VAR THISYEAR            = YEAR  ( TODAY() ) 
    VAR THISMONTH           = MONTH ( TODAY() ) 
    VAR FINANCIALYEAR       = IF ( THISMONTH <= 6, THISYEAR - 1, THISYEAR )  
    VAR FINANCIALYEARNEXT   = FINANCIALYEAR + 1 
    VAR FINANCIALYEARTARGET = FINANCIALYEAR + 4 
RETURN 
    ROW ( 
        "THISYEAR",            THISYEAR, 
        "THISMONTH",           THISMONTH, 
        "FINANCIALYEAR",       FINANCIALYEAR, 
        "FINANCIALYEARNEXT",   FINANCIALYEARNEXT, 
        "FINANCIALYEARTARGET", FINANCIALYEARTARGET
    )

--CheckPoint Next =   -- uncomment for pbi desktop 
/*  Summary     routine to find the checkpoint value for the next financial year 
    psuedo code
    +  find current financial year 
    .  drive off first 4 characters of our financial year format
    -  find the next financial year
    +  find the checkpoint value for next financial year
    History     042023 gt  build
 */
EVALUATE   -- comment out for pbi desktop but needed for daxstudio
    VAR FINANCIALYEARNEXT = SELECTEDVALUE ( 'x Reference Dates'[FINANCIALYEARNEXT] )
    VAR MBM_Id = "148"   -- testing 
    --VAR MBM_Id = 'Measure Benefit Master'[Id]   -- uncomment for pbi desktop 
    VAR CHECKPOINTNEXT = 
        SUMMARIZE ( 
            FILTER ( 
                VALUES ( 'Measure Benefit Progress List' ),  
                VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARNEXT ) && 
                'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
            ),
            [CheckPoint]
        ) 
RETURN 
    { CHECKPOINTNEXT }

--CheckPoint Target =   -- uncomment for pbi desktop 
/*  Summary     routine to find the checkpoint value for the next financial year 
    psuedo code
    +  find current financial year 
    .  drive off first 4 characters of our financial year format
    -  find the next financial year
    +  find the checkpoint value for target financial year
    History     042023 gt  build
 */
EVALUATE   -- comment out for pbi desktop but needed for daxstudio
    VAR FINANCIALYEARTARGET = SELECTEDVALUE ( 'x Reference Dates'[FINANCIALYEARTARGET] )
    VAR MBM_Id = "148"   -- testing 
    --VAR MBM_Id = 'Measure Benefit Master'[Id]   -- uncomment for pbi desktop 
    VAR CHECKPOINTTARGET = 
        SUMMARIZE ( 
            FILTER ( 
                VALUES ( 'Measure Benefit Progress List' ),  
                VALUE ( 'Measure Benefit Progress List'[Financial Year Search] ) = VALUE ( FINANCIALYEARTARGET ) && 
                'Measure Benefit Progress List'[Main ID LkUpId] = MBM_Id
            ),
            [CheckPoint]
        ) 
RETURN 
    { CHECKPOINTTARGET }

[lbendlin]

Remember that RELATED is only needed for calculated columns. Measures use the data model. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523