Delete double row with conditions on previous row

[Anonymous]

Hi all,

I'm trying to create a calculated column to use it as a filter (value 0 of 1) based on the table below. Double row must be filtered out based on the conditions of the previous row based on the table below.

EMP	startdate	enddate	Category	row to be removed
1	1-jan-2019	15-nov-2019	NOV	No
1	16-nov-2019	31-dec-2019	GS	Yes

In this case the last row must be removed and conditions are:

1. EMP value must be the same (double row) and,

2. the Category value of the row to be removed (in this case the second row) is 'GS' and the startdate is on or after 1-october-2019

3. the enddate of row to be kept (in this case first row) is on or after 1-october-2019.

The calculated column should return a value of 1 or 0. 1 for the row to be removed which I can use as page level filter in the report.

Thank you in advance,

Mirza

[v-gizhi-msft · Accepted solution]

hi,

According to your description, please try this measure and column:

EMP-check =

IF(CALCULATE(COUNTROWS(Table25),FILTER(ALL(Table25),Table25[EMP] in FILTERS(Table25[EMP])))>1,1,0)

Row to be removed =

IF([EMP-check]=1&&Table25[Category]="GS"&&(Table25[startdate]>=DATE(2019,10,1)||Table25[enddate]<=DATE(2019,10,1)),1,0)

Here is my test table:

The result shows:

Here is my test pbix file.

pbix 

If you still have questions about it, please for free to let me know.

Best Regards,

Giotto Zhi