Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Sorry for posting what looks like a commonly asked question, but I can't seem to find the right answer. I have two (unrelated) tables:
registrations
event_date
statuses
created_date
status_value
I want to add some columns to registrations:
completed_status_at_event_date
This column need to take the email from registrations, lookup to statuses and return a 1 if the status_value - "Completed" has a created_date before the event_date
completed_status_14days_after_event_date
This column need to take the email from registrations, lookup to statuses and return a 1 if the status_value - "Completed" has a created_date between the event_date and 14 days after.
I just seem to be skirting round the solution and going round in circles.
Hope someone can help.
Thanks,
Stewart
Solved! Go to Solution.
Thanks for the reply. I think I worked out the solution using this forum post, and it seems to work!
CompleteCustomer = VAR EventDate = 'Registrations'[EventDateAsDate] VAR RegEmail = 'Registrations'[Email] RETURN IF ( CALCULATE ( COUNTROWS('Statuses'), FILTER ( 'Statuses', 'Statuses'[Created On] < EventDate && ISERROR(SEARCH("Complete", 'Statuses'[CompleteStatus])) && 'Statuses'[Email] = RegEmail ) ) > 0, "Customer Complete", "Customer Active" )
@stranter I think you do have related tables.. your relation is the email in each table.
Link the 2 tables up with their email and you'll be able to create some simple formulas.
Should look something like this:
completed_status_at_event_date = IF(RELATED('Statuses'[status_value]="Completed" && RELATED('Statuses'[created_date])<'Registrations'[event_date],1,0)
completed_status_14days_after_event_date = IF(RELATED('Statuses'[status_value]="Completed" && RELATED('Statuses'[created_date]) >='Registrations'[event_date] && RELATED('Statuses'[created_date]) <='Registrations'[event_date]+14,1,0)
Thanks for the reply. I think I worked out the solution using this forum post, and it seems to work!
CompleteCustomer = VAR EventDate = 'Registrations'[EventDateAsDate] VAR RegEmail = 'Registrations'[Email] RETURN IF ( CALCULATE ( COUNTROWS('Statuses'), FILTER ( 'Statuses', 'Statuses'[Created On] < EventDate && ISERROR(SEARCH("Complete", 'Statuses'[CompleteStatus])) && 'Statuses'[Email] = RegEmail ) ) > 0, "Customer Complete", "Customer Active" )
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |