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

Is this getting closer?

 

Table 3 = 
VAR T1 = SUMMARIZE('Table','Table'[BATCH#],"First Date" , MIN('Table'[DATE]))
VAR T2 = FILTER(
    NATURALINNERJOIN('Table',T1),[STATE]<> "Aprobado" && [PROCESS]="Tinto")
RETURN SUMMARIZE(T2,'Table'[BATCH#] , "Batches" , COUNTROWS('Table') , "First Date" , min('Table'[DATE]))