Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

3 condition lookup Power Query

Hi All!

I would like to import a column (Sales Person name) to tansactions details query and to do this, i need to meet 3 conditions.
First one is simple, Customer ID needs to match, the second and third are causing me headache as they reporesent transaction months. Every Sales Person has account assignemnts with start and end months. (representing months when this account was assigned to them)

I need the assginment start month to be lower or equal to transaction month, and end month higher or equal to transaction month(the same column) 
As M code is new to me, i am not sure how to allow ">=","<=" parameters to work in custom formulas. 

This is my first post, i hope this is clear enough but would be happy to answer any questions if need to clarify. 

Community Champion
Community Champion

@Mark95 Post sample data or upload the PBIX to google drive and share the link

I have uploaded an example document presented below as well as a snipp of it.
Issue Example Excel file 



Below is the formula i tried using in column E of the example file, it works well on small tables but the one my query generets is 100k+ lines, with the lookup table being 40k+ lines.

=IFERROR(INDEX(Lookup_Table[Sales Rep],MATCH(1,(Lookup_Table[Account ID]=[@AccountID])*(Lookup_Table[Assignment start month year]<=[@YearMonth])*(Lookup_Table[Assignment End Month Year]>=[@YearMonth]),0)),"Account not Assigned")

Hope this helps.

Thank you for taking a look into this, hope i will in the future 🙂

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors