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 t...
  • 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.

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.