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

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Kudoed Authors