Trying to filter the results to show just the last week of my last year

[lcfaria]

Hi all, I need help.

I have a simple table with the following columns:

So I created a measure to calculate what my max year is and what my max week is, and then do a distinct count of my managers. I also applied a filter to bring only ratings equal to Bad, Medium or Good.

As example, for week 7 2020, I have this result (2 distinct managers):

However, when I use this on a card, and I apply a filter only for rate "Good", the result should be 0 (because I don't have any manager with rate "Good" for week 7 2020), but on my card the result returned is being 2.

In a table, values are also being returned for other weeks and not just week 7 2020.

I don't know where I'm going wrong and would like some help from the community.

PS: I don't have a calendar table and I don't want to use it for this specific case.

Here is the link to this file: https://1drv.ms/u/s!At5JlMR_naI1gf4wTW_SSm3pNmwSSA

[jdbuchanan71 · Accepted solution]

@lcfaria 

In the matrix you were running into problems with the filter context for when the variables are calculated and in the card it was CALCULATE reading 'Fact'[Rating] in {"Bad","Medium","Good"}) that was ignoring your filter.  We can fix these with some ALL and KEEPFILTERS.

Measure = 
VAR MaxYear = CALCULATE ( MAX('Fact'[Year]), ALL ('Fact') ) -- This correctly returns the last year (Year 2020)
VAR MaxWeek = 
CALCULATE(
    MAX('Fact'[Week]),
    ALL('Fact'),
    'Fact'[Rating] in {"Bad","Medium","Good"},
    'Fact'[Year] = MaxYear
) -- This correctly returns the last week of the year (Week 7)
VAR Result =
CALCULATE(
    DISTINCTCOUNT('Fact'[Manager]),
    KEEPFILTERS('Fact'[Rating] in {"Bad","Medium","Good"}),
    KEEPFILTERS('Fact'[Week] = MaxWeek),
    KEEPFILTERS('Fact'[Year] = MaxYear)
)
RETURN
Result

[lcfaria]

@jdbuchanan71 this worked exactly as expected, thank you very much!