Forum Discussion

Aimeeclaird's avatar
Aimeeclaird
Helper IV
5 years ago
Solved

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

 

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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]
    )
)

4 Replies

    • Aimeeclaird's avatar
      Aimeeclaird
      Helper IV
      5 years ago

      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's avatar
        CNENFRNL
        Community Champion
        5 years ago

        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]
    )
)