Forum Discussion

ratercero's avatar
ratercero
Helper III
9 years ago
Solved

Filter Query (or create new table) based on first (date) duplicate and values of 2 columns

Hello Everyone, I've tried my best to find the best solution for my issue on the forums, but got into a dead end, so im asking for help from scratch. instead of modifying my attempts, here it goes: ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Aha, I think I follow now

     

    Table 3 = 
VAR T1 = SUMMARIZE('Table','Table'[BATCH#],"First Date" , MIN('Table'[DATE]))
VAR T2 = FILTER(
    NATURALINNERJOIN('Table',T1),[STATE]<> "Aprobado" && [PROCESS]="Tinto" && [First Date] = [DATE])
RETURN T2
ratercero's avatar
ratercero
Helper III
9 years ago

Hello Phil,

This is close but not exactly what I'm looking for, this is my logical thinking of how it should happen:

 

First I need to find the earliest a batch is in the data, no matter if it complies with the other filtered criteria, afterwards it should filter only leave on the table the ones that comply. 

 

Basically im looking for all batches that in their first input comply with the criteria status<> "aprobado" and process  ="Tinto".

 

This is what i got with your suggested formula:

 

 They comply with the filters but have duplicate batch #.

I hope I explained myself properly this time.

Any more thougths?

Thanks

 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Can you please post a mock-up of what the result table should look like?  

 

 

  • ratercero's avatar
    ratercero
    Helper III
    9 years ago

    Same as the one in my last post but only with the top result which is the ealiest date:


     

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      Aha, I think I follow now

       

      Table 3 = 
VAR T1 = SUMMARIZE('Table','Table'[BATCH#],"First Date" , MIN('Table'[DATE]))
VAR T2 = FILTER(
    NATURALINNERJOIN('Table',T1),[STATE]<> "Aprobado" && [PROCESS]="Tinto" && [First Date] = [DATE])
RETURN T2
      • ratercero's avatar
        ratercero
        Helper III
        9 years ago

        Thank you!