DAX window functions in practice
Register at Meetup Window functions are a new addition to DAX launched in December 2022. They aim to simplify certain data operations which are very intuitive in Excel, but weren't so easy to replicate in DAX so far. In this session we'll cover the 3 main window functions: INDEX, OFFSET and WINDOW to build meaningful insights. We'll see the power of these window functions by simplifying a previously lengthy DAX code and speeding up comparison-type analysis. We'll leverage the window functions to build a Pareto chart, a Cumulative chart and calculate delta comparing to last item.
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!
