I need help with my data model in Power BI. I'm buldling a report that shows how job search users are using a custom app.
User Stages are defined based on AcceptDate from FactRegistration table (within 30 days = New User), LoginDate into the app from FactLoginsDaily table (active in app within 30 days or not), and Job Placement Status from FactPlacementStatusChange table (where status is either blank, No Placement (cancel/decline/complete), Pre-Booking, Booked, Working). From these criteria, there are 6 possible User Stages (New User, No Status, Pre-Booking, Booked, Working, Fell Off). The goal is to have these User Stages as columns in a visual that shows the count of users in each stage based on monthly/daily login activity in the app.
There are 242,000 UserIDs in FactRegistration table, 8 million rows in FactLoginsDaily, and 13 million rows in FactPlacementStatusChange. I first tried using Measures only to calculate if a user is a New User with No Placement Status/Blank, based on the placement change timestamps and a date slicer that gives the latest date; but the calculation eventually timed out as the measures got more complex. Now I'm trying to use calculated columns in the FactLoginsDaily table, so for each UserID and LoginDate, a column indicates if they are a new user (accept date within 30 days of the login date), and trying to bring in the MAX placement stage on or before the login date based on the placement change timestamp -- but it's having to iterate over millions of rows and eventually times out with a memory error. Am I thinking about this all wrong?
