Forum Discussion

hiolgh's avatar
hiolgh
Frequent Visitor
1 year ago
Solved

FILTERing a value from a RELATEDTABLE

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's avatar
    techies
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      hiolgh
      Frequent Visitor
      1 year ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      hiolgh
      Frequent Visitor
      1 year ago

      Thanks for taking the time to recreate the tables!