Filter Chart by Min Date

[reast]

My data set has client IDs, dates of a screening tool, and the responses. I'm trying to compare our clients' responses from their first visit to their last. I have created these measures which when put into a table, returns the cooresponding dates for each client.

FirstDate = CALCULATE(MIN('Hosp'[SCREEN_DATE]),VALUES('Hosp'[ID]))

LastDate = CALCULATE(MAX('Hosp'[SCREEN_DATE]),VALUES('Hosp'[ID]))

 What I would like to do next is use these dates to filter the data in a chart. One chart showing the responses from their first visit and another showing responses from their last visit. I tried this formula for creating a new column

Is Latest = if(Hosp[SCREEN_DATE] = Hosp[LastDate], "Latest", "")

but it returns "Latest" for every row instead of one per client.

Any insight would be greatly appreciated. Thanks!!

[TomMartens]

Hey,

here you will find a solution to your question, in this example a calculated column is created (necessary to be used in visuals as a filter) looking for the latest date of a ticket. To achieve this it's necessary to switch from the implicit ROW CONTEXT into a FILTER CONTEXT using CALCULATE. Now the FILTER CONTEXT is expanded to all the columns by using ALLEXEPT, except Ticketnumber (your client column) and the column that is used in MAX expression.

If you are familiar with windowing functions in SQL the columns used in ALLEXCEPT are the columns used in the PARTITON BY ... ORDER BY ... part of the OVER() statement.

Hope this helps 

[reast]

Thanks for the quick response. However, I'm having the same problem that it is calculating Yes for each row, not one for each client. Below is the measure, but I didn't have two fields (date and time) like in your example. Is that what's causing the problem? 

IsLatest =
IF('Hosp'[SCREEN_DATE] =
CALCULATE(
    MAX('Hosp'[SCREEN_DATE]),
    ALLEXCEPT('Hosp',Hosp[ID],Hosp[SCREEN_DATE])
),"Yes", "No")

[TomMartens]

Hey,

you have to use this as calculated column in your 'Hosp' table.

If this doesn't work, can you please provide sample data. If you are talking about client - does this correspond to your [ID] column?

[TomMartens]

Now I see - you have to remove your date column from the ALLEXCEPT() function. The column still adds to FILTERCONTEXT for this reason the date is always the max date - because there is just one date.

[reast]

Great, thank you so much! The Latest (Max date) works perfect. I made another for Min date and it isn't working. I'm not sure why. I've copied both below and a screenshot of the table results. Only clients with one date shows Yes for both earliest and latest. Otherwise, the earliest says no. The ID corresponds to a client. Thanks!

IsLatest =
IF('Hosp'[SCREEN_DATE] =
CALCULATE(
    MAX('Hosp'[SCREEN_DATE]),
    ALLEXCEPT('Hosp',Hosp[ID])
),"Yes", "No")

IsEarliest =
IF('Hosp'[SCREEN_DATE] =
CALCULATE(
    MIN('Hosp'[SCREEN_DATE]),
    ALLEXCEPT('Hosp',Hosp[ID])
),"Yes", "No")

[TomMartens]

Hey, 

you also have to remove the filter that is applied by each "Calculated Column",

IsLatest = 
IF(... = 
CALCULATE(MIN(...)
,ALLEXCEPT(..., 'Hosp'[IsEarliest])
), ...

and vise versa

Good night

[TomMartens · Accepted solution]

Please mark the post as answer, so that other may benefit.

Cheers

[reast]

Thank you. I was able to add [IsEarliest] to the ALLEXCEPT in IsLatest, but when I tried to add [IsLatest] to the ALLEXCEPT for IsEarliest, I got the error "A circular dependency was detected: Hosp[IsEarliest], Hosp[IsLatest], Hosp[IsEarliest]."

IsEarliest =
IF('Hosp'[SCREEN_DATE] =
CALCULATE(
    MIN('Hosp'[SCREEN_DATE]),
    ALLEXCEPT('Hosp',Hosp[ID], Hosp[IsLatest])
),"Yes", "No")

Never mind, my fault, I had a filter on that was hiding some of the Earliest Yes. It works. Thank so much for your help!