Count of Only Most Recent Value

[Cmcmahan]

I'm guessing that I'm missing something obvious, but I have survey data and the relevant columns are set up like this:

User	Survey_Date	Response
Alex	20221201	Yes
Barry	20221225	Maybe
Carl	20221226	No
Alex	20220601	No
Barry	20220625	Maybe

I would like to get a graph that looks like this, counting the number of users who answered each category:

My issue is that if I use straight DISTINCTCOUNT, if a user changed their answer between surveys in a specified year, it will count them in both response groups.  I have a business requirement to use the most recent answer.

If I try to use TOPN on the survey date, it gets only the most recent survey date for all users, in the above case, only counting Carl's single answer.  What I want is to TOPN each user's answers.

I'm guessing there's some solution with SUMMARIZECOLUMNS that I'm not seeing, but any guidance would be appreciated.

[amitchandak · Accepted solution]

@Cmcmahan , refer if this approach can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...