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
DreDre
Helper II
Helper II

Fuzzy Date Match

I know there is a solution for this, but I keep getting lost in the variations, but In essence I am trying to do an vlookup with 2 lookup values in one table referencing a lookup table. 

 

My goal is to add the associated bank number based on the date/city/time combo in Data_Table and add that number as a new column. I have seen multiple variations, but I am trying to do this with a single calculated column. I think that means I need to use VAR for part of the formula, but I keep getting lost. 

 

Bank_Table

CityBank#Start DateEnd DateStart TimeEnd Time
Dallas101/01/20207/31/202000:0008:00
Dallas2

01/01/2020

7/31/202008:0116:30
Dallas301/01/20207/31/202016:3123:59
Dallas16/1/202012/31/202000:0010:00
Dallas26/1/202012/31/202010:0117:30
Dallas36/1/202012/31/202017:3100:00
Austin11/1/2020

7/31/2020

00:0011:30
Austin21/1/20207/31/202011:3120:30

etc. this table continues on and covers a few years and multiple cities worth of data. 

 

 

Data_Table

CityDateTime(New Bank Column - Future State)
Dallas2/5/202011:522
Dallas7/20/202008:001
Austin4/2/202011:251
Dallas10/10/202022:003

 

Request:

Can someone please help me with the DAX formula to populate the Bank Column based on the Bank_Table as a lookup?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Like this?

New Bank Calculated Column =
CALCULATE (
    SELECTEDVALUE ( Bank_Table[Bank#] ),
    Bank_Table[Start Date] <= Data_Table[Date],
    Bank_Table[End Date]   >= Data_Table[Date],
    Bank_Table[Start Time] <= Data_Table[Time],
    Bank_Table[End Time]   >= Data_Table[Time]
)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Like this?

New Bank Calculated Column =
CALCULATE (
    SELECTEDVALUE ( Bank_Table[Bank#] ),
    Bank_Table[Start Date] <= Data_Table[Date],
    Bank_Table[End Date]   >= Data_Table[Date],
    Bank_Table[Start Time] <= Data_Table[Time],
    Bank_Table[End Time]   >= Data_Table[Time]
)

I knew this had to be easier than I was making it out to be! Thank-you so much for your help @AlexisOlson this worked perfectly!

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.