Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stranter
Frequent Visitor

How to lookup value from another table in date range, using date?

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

email

 

statuses

created_date

email

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

 

 

1 ACCEPTED 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"
	)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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"
	)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.