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.

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!