Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |