cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aimeeclaird
Helper III
Helper III

Look up values in table based on multiple criteria

Hi, apologies if the subject isn't clear. 

 

I have attached a link to a data file (I can't share my real scenario due to type of data). 

 

My data comes from a CRM system, each row is a 'Submission', I have appended the data to ensure I have one column for each category (?) of data however to improve UX in the CRM, we no longer capture all the same data for one of the 'submissions'. E.g. Submission form 1 -  has 15 fields of data captured, but submission form 5 is used specifically for an 'added extra'.

 

The added extra form only has 5 fields in the CRM, however the added extra is only sold with a specific thing. 

 

Please see the example data file to help this make sense.

 

I am looking for someone to advise how I can 'look up' the data e.g.

Value of sale =

If SALE TYPE <> "Dog" then [SALE TYPE] else if

[SALE TYPE] = "Dog" then FIND (?) matching ID and "Cat" and use that rows SALE TYPE 

 

I hope this makes sense and I have given enough detail of what I am looking for. 

Many thanks in advance  

 

Dogs and Cats example data.xlsx

 

 

1 ACCEPTED SOLUTION

Hi, @Aimeeclaird , you might want to try translating your pseudo-code with DAX syntax in a calculated column

Lookup = 
IF (
    Sales[Sale Type] = "Dog",
    MAXX (
        FILTER ( Sales, Sales[ID] = EARLIER ( Sales[ID] ) && Sales[Sale Type] = "Cat" ),
        Sales[Date of Sale]
    )
)

Screenshot 2020-12-15 110601.png

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User IV
Super User IV

@Aimeeclaird I would use a SWITCH statement and then this will provide you some syntax for how to "lookup" values that need multiple criteria.

LOOKUPVALUE Range - Microsoft Power BI Community


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler 

Sorry, I am still struggling with this. I've tried adapting the dax formula you shared and to incorporate SWITCH however I am not sure I understand properly so i'm not using the formula correctly.

 

How do I write:

If [Sale Type] = "Dog" 

Find where [ID] = [ID] AND [Sale Type] = "Cat" 

Return [Date of Sale] from matching row 

 

Any help or direction to an artical that explains would be great.

Thanks in advance

Hi, @Aimeeclaird , you might want to try translating your pseudo-code with DAX syntax in a calculated column

Lookup = 
IF (
    Sales[Sale Type] = "Dog",
    MAXX (
        FILTER ( Sales, Sales[ID] = EARLIER ( Sales[ID] ) && Sales[Sale Type] = "Cat" ),
        Sales[Date of Sale]
    )
)

Screenshot 2020-12-15 110601.png

View solution in original post

Thank you so much @CNENFRNL for helping me translate what I needed! Greatly appreciated.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors