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
Mark95
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. 

2 REPLIES 2
AntrikshSharma
Community Champion
Community Champion

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

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

Mark95_0-1670425773785.png

 

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

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.

Top Solution Authors
Top Kudoed Authors