Look up values in table based on multiple criteria

[Aimeeclaird]

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

[Greg_Deckler]

@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

[Aimeeclaird]

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

[CNENFRNL · 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]
    )
)

[Aimeeclaird]

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