Filter Min (date) on two conditions in another column

[PierreCA]

I want to get the last date of an Open demand of a table on two conditions: 
1. The Situation  is Open

2. The Situation  is not Solved

With those conditions,

I should see the demand no. 2 and the date 2022-10-04

I should not see the demand no1 and the date 2022-01-01, as it is solved. 

ID	F_iD_Demand	Date	Situation
1	1	2022-10-01	Open
2	1	2022-10-02	Assess
3	1	2022-10-03	Solved
4	2	2022-10-04	Open
5	2	2022-10-06	Assess

I guess there's a a double filter somewhere  but don't know where. 

[lbendlin]

Power Query or DAX ?

[PierreCA]

Dax. 🙂

[lbendlin]

Here's a measure.  Frankly, this could have been a calculated column too.

Open Date = if("Solved" in VALUES('Table'[Situation]),BLANK(),minx(filter('Table',[Situation]="Open"),[Date]))

[PierreCA · Accepted solution]

But if Demand no 1 isn't solved and no 2 is, you'd get the same answer. which is not right. 

Finally, the best solution I found is the following:

Calculate (
          Min ( table}[datefield]),
          table[Situation]=Open, 
         NOT (table[Situation]=Open && table[Situation]=Solved))
          )

Thanks @lbendlin  🙂 

[lbendlin]

NOT (table[Situation]=Open && table[Situation]=Solved)

This makes no sense as it always evaluates to TRUE  (and it's missing the double quotes)

[PierreCA]

@lbendlin , 🙂

It makes sens, since I want to discard  demands solved from the result. The only way to identify them is by that conditions. Otherwise, the program will take only a look if Situtation is Open. That's the case to all demands at the beginning and report the date of the wrong demand.

 

[lbendlin]

No, you need to rewrite it.

NOT table[Situation] IN {"Open","Solved"}

[PierreCA]

Hello @lbendlin ,  🙂

I've try your proposition and, unformtunately, it failed. 

The question is then why? 

I believe that's because your proposition evaluate Situation is either Open or Solved.  And because the program always find at least an Open, is neutralise the precedent condition.  As a result = Blank. 

In mine, I do evaluate if a demand is not Open and Solved. I only want to keep a demand Open only. That's why, with  solution I will not return demand no 1, as it's both Open and Close; but return no 2, as it is only Open.

Thanks for your insightful.