Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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!


Super User
Super User

Hi @soupyNoodles it seems you need standard star schema model in Power BI. Service seems like sales in Contoso model for Power BI.

For last sales / service date according to this model on you can use something like below (simple max date of sales) with few columns 


Check link for example of diagram on

On the link below 


Hope this help, kudos appreciated.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Apologies for the slow reply. While this does help if my output is a row-level table, what I intend to do is create a visualization. For instance, if I wanted to show a breakdown at the race/ethnicity level, the challenge is that the measure above selects the latest date as of the whole dataset. I have tried many variations of this using ALLEXCEPT, for instance, and I need the output to select the latest date as of a date slicer PER PERSON and then identify the relevant race/ethnicity for my visualization.  

Hi @soupyNoodles please share soome sample data of your inputs and expected output.

Yes, in example above max, last date is for overall data. So we need your details.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors