How to get related value from table related two tables apart?

[olimilo]

We have our tables setup like this:

Project		rel		Sample		rel		Test
ProjectNo		←1:M→		ProjectNo				TestNo
DueDate				SampleNo		←1:M→		SampleNo

Is it possible for me to get the value of the Project[DueDate] column from the Test table as a custom column? I tried using RELATED() but I'm getting the error 'doesn't exist or doesn't have a relationship to any table in the current context'. Specifically, I am trying to convert this code code into DAX:

CASE
    WHEN CAST(TEST.DATE_REVIEWED AS DATE) > CAST(PROJECT.DUE_DATE AS DATE) THEN 1
    ELSE 0
    END AS 'OTD'

I tried using the DAX formula below but I am getting a circular reference error:

OTD = 
    VAR _tbl = RELATEDTABLE('Project')
    VAR DueDate =
        CALCULATE(
            MIN('Project'[DueDate]),
            _tbl
        )
    
    RETURN
        IF(ISBLANK(DueDate), 0, 1)

[Anonymous]

Hi @olimilo ,

Here I create a sample to have a test.

It seems that OTD column works well in this sample.

You can try to download my sample to compare the difference between yours and mine.

If this reply still couldn't help you solve your issue, please share a easy sample file with us. And you can show us the screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @olimilo 
Related functions can be used when tables have an active relationship.
If in your case they are not you can use lookupvalue ()

https://www.youtube.com/watch?v=jUaEdwN9J1I

For more detailed suggestions

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Irwan]

hello @olimilo 

i dont know how your table looks like, but to extract value from two table apart is possible.

there are couple ways to do this, but here is an example.

Table 1 :

Table 2 : 

create a calculated column for DueDate to extract value from Test with following DAX.

DueDate = 
MAXX(
    FILTER(
        'Table 1',
        'Table 2'[ProjectNo]='Table 1'[Project]&&
        'Table 2'[SampleNo]='Table 1'[Sample]
    ),
    'Table 1'[Test]
)

you can do with LOOKUPVALUE as well, here is the link.

Re: Looking for value in 2nd table based on the va... - Microsoft Fabric Community

Hope this will help.

Thank you.