Forum Discussion
Helper IIStumped, cannot get min date
SOLVED
This is really crazy to me, so I would love an explanation from someone.
Data is such that a value for study_seq will always have the same value for title-id and vice-versa. I changed my formula to use title-ID which DOES appear in the table instead of study_seq. I now get the correct response.Someone, Ritaf1983 ? maruthisp ? please explain this too me.
Super UserHi jjustjjo
It’s not impossible, it’s filter context. Because the date columns are in your table visual, each row filters the measure down to that single date, so MIN just returns the current row. FILTER(‘Milestones Info’, ...) doesn’t help either, since it iterates the table after that filter is applied.
Remove the row-level filters and keep only the study:
Min StLevCompYear = VAR _min = CALCULATE( MIN('Milestones Info'[StudyLevel Completed Date]), ALLEXCEPT('Milestones Info', 'Milestones Info'[study_seq]) ) RETURN YEAR(_min)
This returns 2023 on every row for study 52.
Note: ALLEXCEPT also ignores slicers on other columns of that table. If you have those, use REMOVEFILTERS on just the columns in your visual instead:
CALCULATE( MIN('Milestones Info'[StudyLevel Completed Date]), REMOVEFILTERS('Milestones Info'[StudyLevel Completed Date], 'Milestones Info'[SiteLevel Completed Date], 'Milestones Info'[Milestone]), 'Milestones Info'[Milestone level] = "Study" )
Same idea fixes your line chart: if the axis uses a date table, add it to REMOVEFILTERS inside the measures that feed the DATEDIFF.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.