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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.