Entry with Max Date
Apologies if this is posted somewhere else already. My fact table is as such: there is an entry for each person's interaction with a company. There is a date associated with each interaction and the person's race/ethnicity, age, and gender are recorded at each service date. I am throwing into a dashboard various visualizations for these people but I want to filter them by the latest service they had; a person's race/ethnicity can be incorrectly recorded (so I want the latest one which is recorded at the latest service), and a person's gender can change over time, as does the age. I am building the source table via SQL and would normally use a lead () window function but I want to achieve this in Power BI. If, say, I were to apply an IsLatestInteraction = 1 filter from SQL Server to the Power BI visualization, and restricted the service dates from say January 1, 2023, to March 1, 2023 in Power BI via a service date slicer, the visualization would miss people who were serviced in June 2023 because that is when their latest service was. I want to build a measure (if that is what I should be using??) where it calculates the person's latest age, race/ethnicity, and gender AS OF the date slicer selected. I am confused between the various Power BI functions such as ALL and ALLEXCEPT but I believe some sort of DAX function needs to be used. Please let me know what is best! Thanks in advance!