I’ve spent a couple of days trying to figure this out, but alas it’s beyond me.
The functionality I need to be able to crete is sort of presented here:
The user selects a date from the timeline, then chooses a date range before (14 days in this case), and date range after (42 days).
I have to show the customers in the right side table who had an appointment in category 1 or 2 (shown as an aggregation in the Manhattan at the bottom, and details the table on the left) in Locations 6 or 33 (left table) who then went on to have a subsequent appointment of Category 1 or 2 in Location 3 in the date range selected (42 days after 8 July 2019).
I have written DAX to slice the tables before and after the selected date, by the selected date ranges, but can’t figure out how to present the result as the intersection of these tables, and I’m sure I’m missing something simple/obvious here.
Or maybe not.
I really want to do this in DAX so I can later make this easier to repeat this for different locations in the left and right tables (“Before” and “After”) instead of pushing this to Power Query which would mean re-writing every time a different department has an idea for another analysis.
I’ve removed these measures I wrote to present the tables except for the aggregation from the .pbix to simplify things, but can put them back in OK. (The table on the left now just has a filter applied to the visual).
The .pbix is real data, greatly simplified and anonyised.
Appointments (transactions) have been collapsed to 1/null for the 4 different categories.
DAX Intersection problem.pbix (651.8 KB)
Any help greatly appreciated.