Forum Discussion

LithanaM's avatar
LithanaM
Frequent Visitor
4 years ago

Filtering ID & date compared within two date columns and many lines for this ID

Hi beautiful community, 

I have a question that I am not sure can be solved, but there are so many possibilities with PBI and so high skilled people in there that I am taking a chance 🙂

I am following several students stages with a beginning of stage date and end of stage date through different stages.

Below the exemple of one Student.

The data have been cumulated each year and unfortunately, I have some stages that were projected and did not happen that are still in the Database.

Date where in the column FinStage-RecMin will be above zero are projections, but some of them are to be kept them

For each selected student, I have to eliminate stages where the projected start dates (FinStage-RecMin>0) are already included in another stage for this student.

 

In the example below, I have to eliminate the line 28 juillet 2020 (FinStage-RecMin= 276) that is included in the stage 27 juillet to 27 September (in the line above).

 

Is there a way to do that?

 

Thank you all for your help!

 

Nathalie

 

 

 

Need Help

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, LithanaM 

     

    If you are trying to eliminate the (FinStage-RecMin>0) rows, you can try the following method.

    Original table:

    Table:

    Table 2 = 
Var table1=SUMMARIZE('Table','Table'[Date],'Table'[FinStage-RecMin],'Table'[Stage])
Var table2=FILTER(table1,[FinStage-RecMin]<=0)
return
table2

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • LithanaM's avatar
      LithanaM
      Frequent Visitor
      4 years ago

      Bonjour, 

      Unfortunately, I can't just eliminate the >0 lines, because some of them are unique and have to be kept.

      I actually work with historical data that were cumulated and where duplicates were eliminated, but it was not always the most recent entries that were kept, hence the problem I am having now....

      I didn't have an example in this table, but I can have a finstage-recmin>0 but that does not exist in other instances and need to be kept.

      Nathalie