topic Re: FILTERing a value from a RELATEDTABLE in DAX Commands and Tips

FILTERing a value from a RELATEDTABLE

hiolgh — Tue, 06 May 2025 16:53:49 GMT

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(

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.

Re: FILTERing a value from a RELATEDTABLE

techies — Tue, 06 May 2025 20:38:21 GMT

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

)

Re: FILTERing a value from a RELATEDTABLE

Anonymous — Wed, 07 May 2025 04:33:29 GMT

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.

Re: FILTERing a value from a RELATEDTABLE

hiolgh — Thu, 08 May 2025 09:26:27 GMT

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.

Re: FILTERing a value from a RELATEDTABLE

hiolgh — Thu, 08 May 2025 09:33:52 GMT

Thanks for taking the time to recreate the tables!