Why is this filter not working properly?

[andytmcc]

Hi, 

I am creating a calculated column that pulls in a figure from the previous years row. I only want this figure to be pulled in when my [VARIABLE] column is equal to "Parliamentary Constituency". However, one value ('Mid Ulster') in my [Category] column appears in "Parliamentary Constituency" as well as in "LGD" which are both in my [VARIABLE] column. I only want the value to be created for "Parliamentary Constituency" not for LGD. It seems that my filter is not quite set up correctly: no values are being created for other "LGD's" (which is good), it is just being created for the one "LGD" that has the same name (Mid Ulster) that appears in the "Parliamentary Constituency".  I don't understand why && 'Loneliness All Data'[VARIABLE] == "Parliamentary Constituency" isn't restricting it to just "Parliamentary Constituencies". Sorry if this sounds confusing!

LOC Previous Year =

VAR PreviousRow =

    TOPN (

        1,

        FILTER (

            'Loneliness All Data',

            'Loneliness All Data'[YEAR] < EARLIER ( 'Loneliness All Data'[YEAR] )

                && 'Loneliness All Data'[CATEGORY] = EARLIER ( 'Loneliness All Data'[CATEGORY] ) && 'Loneliness All Data'[VARIABLE] == "Parliamentary Constituency"

        ),

        [YEAR], DESC

    )

VAR PreviousValue =

    MINX ( PreviousRow, [Locus Of Control Mean] )

RETURN

    PreviousValue

[andytmcc]

That's fantastic @johnt75  It seems to have done the trick. Thank you. Am I correct in saying I could remove the && 'Loneliness All Data'[VARIABLE] == "Parliamentary Constituency"  from within the filter on line 11 as it's not needed there anymore?

[johnt75]

No, its still needed there to make sure you get the correct rows when Mid Ulster is in CATEGORY

[johnt75]

Wrap the whole calculation inside IF('Loneliness All Data'[VARIABLE] = "Parliamentary Constituency").

Your filter is restricting the rows returned by TOPN, but it is not doing a check to see if it should perform the calculation at all for the current row.

[andytmcc]

Ah ok. Thanks @johnt75  Could you show me how to do that? I'm pretty much a newbie!

[johnt75 · Accepted solution]

LOC Previous Year =
IF (
    'Loneliness All Data'[VARIABLE] = "Parliamentary Constituency",
    VAR PreviousRow =
        TOPN (
            1,
            FILTER (
                'Loneliness All Data',
                'Loneliness All Data'[YEAR] < EARLIER ( 'Loneliness All Data'[YEAR] )
                    && 'Loneliness All Data'[CATEGORY] = EARLIER ( 'Loneliness All Data'[CATEGORY] )
                    && 'Loneliness All Data'[VARIABLE] == "Parliamentary Constituency"
            ),
            [YEAR], DESC
        )
    VAR PreviousValue =
        MINX ( PreviousRow, [Locus Of Control Mean] )
    RETURN
        PreviousValue
)