FILTERing a value from a RELATEDTABLE

[hiolgh]

Hi all

I have two tables, 'LGZ Schools' (1) <---> (*) 'CountYourCarbon'.

They're joined as follows: 'LGZ Schools'[_School ID] <---> 'CountYourCarbon'[sfct_letsgozero]

'CountYourCarbon' has three fields: [sfct_letsgozero], [CYC Date] and [CYC Score (Tonnes)].

I'm trying to read a value from CountYourCarbon into a new column called "Baseline" in LGZ Schools. I want to filter CountYourCarbon for where there's a match on [_School ID]<--->[sfct_letsgozero], which will return between 0 and 2 rows, then filter that for the earliest date in the [CYC Date] field, and then return the value in the field [CYC Score (Tonnes)].

The best I've got so far is:

Baseline =

    SELECTCOLUMNS(

        FILTER(

            FILTER(

                RELATEDTABLE(CountYourCarbon),

                'CountYourCarbon'[sfct_letsgozero]='LGZ Schools'[_School ID]

                ),

            [CYC Date]=MIN([CYC Date])

        )

        "Baseline",

        [CYC Score (Tonnes)]

    )

I'm getting the error "The syntax for '"Baseline"' is incorrect."

Can you please advise?

Thanks.

[techies · Accepted solution]

Hi @hiolgh please try this calculated column

Baseline =

VAR RelatedRows =

    FILTER (

        CountYourCarbon,

        CountYourCarbon[sfct_letsgozero] = 'LGZ Schools'[_School ID]

    )

VAR EarliestDate =

    CALCULATE (

        MIN (CountYourCarbon[CYC Date]),

        RelatedRows

    )

RETURN

    CALCULATE (

        MAX (CountYourCarbon[CYC Score (Tonnes)]),

        FILTER (

            RelatedRows,

            CountYourCarbon[CYC Date] = EarliestDate

        )

    )

[Anonymous · Accepted solution]

Hi @hiolgh ,
Thank you @techies for the prompt response!

Upon reviewing the information provided,I tried to create it locally.
 Try using the below calculated column:

Baseline =

VAR SchoolID = 'LGZ Schools'[_School ID]

VAR FilteredTable =

    FILTER (

        'CountYourCarbon',

        'CountYourCarbon'[sfct_letsgozero] = SchoolID

    )

VAR MinDate =

    CALCULATE (

        MIN ( 'CountYourCarbon'[CYC Date] ),

        FilteredTable

    )

VAR Result =

    CALCULATE (

        MAX ( 'CountYourCarbon'[CYC Score (Tonnes)] ),

        FILTER (

            FilteredTable,

            'CountYourCarbon'[CYC Date] = MinDate

        )

    )

RETURN

    Result

Please refer the screenshot and the file for your reference.

If the solution meets your requirement,consider accepting it as solution.

Thank you for being a part of Microsoft Fabic Community Forum!

Regards,
Pallavi.

[hiolgh]

Thanks for taking the time to recreate the tables! 

[techies · Accepted solution]

Hi @hiolgh please try this calculated column

Baseline =

VAR RelatedRows =

    FILTER (

        CountYourCarbon,

        CountYourCarbon[sfct_letsgozero] = 'LGZ Schools'[_School ID]

    )

VAR EarliestDate =

    CALCULATE (

        MIN (CountYourCarbon[CYC Date]),

        RelatedRows

    )

RETURN

    CALCULATE (

        MAX (CountYourCarbon[CYC Score (Tonnes)]),

        FILTER (

            RelatedRows,

            CountYourCarbon[CYC Date] = EarliestDate

        )

    )

[hiolgh]

This is great thanks, and I'll learn from your approach. I had tried using variables but then I couldn't manipulate RelatedRows with further filters. I now see that using it to filter the original table is the way to go.