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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Power_It_Up
Helper I
Helper I

Lookup with two criteria

Hi there.  Converting from Tableau, so please be gentle!

 

I have two tables:-

 

Power_It_Up_2-1698769631680.png

 

In the ORDER table, I am wanting to return the nearest [DEPT] match based on the DEPT table.

 

So for Case Refs 123 and 456, it should return "A" as the [Order Date] falls between the [Date Change] dates in the DEPT table.

Cases 789 and 987, returns "B" and so forth.

 

I have the DEPT table sorted by [Name] then ascending [DATE CHANGE].

 

Many thanks for your valued advice in advance. 🙂

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, I am not sure how your datamodel looks like but please check the below if it suits your requirement.

It is for creating a new column.

 

Jihwan_Kim_0-1698816191334.png

 

 

Department CC =
MAXX (
    FILTER (
        Department,
        Department[Date Change]
            = MAXX (
                FILTER (
                    Department,
                    Department[Name] = 'Order'[Name]
                        && Department[Date Change] <= 'Order'[Order Date]
                ),
                Department[Date Change]
            )
    ),
    Department[Dept]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
Power_It_Up
Helper I
Helper I

@Jihwan_Kim Many thanks for your reply.  This helped me to resolve my issue. 🙂

Jihwan_Kim
Super User
Super User

Hi, I am not sure how your datamodel looks like but please check the below if it suits your requirement.

It is for creating a new column.

 

Jihwan_Kim_0-1698816191334.png

 

 

Department CC =
MAXX (
    FILTER (
        Department,
        Department[Date Change]
            = MAXX (
                FILTER (
                    Department,
                    Department[Name] = 'Order'[Name]
                        && Department[Date Change] <= 'Order'[Order Date]
                ),
                Department[Date Change]
            )
    ),
    Department[Dept]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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