Ignoring specific entries for given key column

[saurav_0101]

Hello Experts!!

I am not sure how to put this in language. I have a situation, please see the below table:

Item No.	Status	PO. No.
A123568	Delivered	25645785
A127895	Cancelled	25345281
A127895	Delivered	25345281
A127895	waiting	26845815
A125698	waiting	21892960
A178546	Cancelled	23524398
A134578	Delivered	23524398
A134578	Cancelled	23524398
A178659	Cancelled	29350885
A178456	waiting	21047787
A145876	Delivered	28745655

As you can see from the image with various colours, for any given item number, I want to ignore the row which is having status as cancelled (like row number 3 and row number 9) whereas for the same item number and PO number I have a status other than cancelled.

If however, I have a cancelled status row which is having a unique item number or PO number, I don't want to ignore them.

So here out of row no. 3 and 4 have the same item no. and PO. number, I want to ignore 3 as row 4 is not cancelled, while 3 is cancelled.

Similarly, I want to ignore row 9 and keep row 8.

But I want to keep rows no. 7 and 10 because although they are cancelled, they have a unique Item no. or PO. No.

I am looking for some measure/column to do so.

I hope I was able to explain. Thanks in advance.

Saurav

[v-rzhou-msft · Accepted solution]

Hi @saurav_0101 ,

I think you can try this code to create a [Flag] column by calculated column.

Keep or Ignore = 
VAR _COUNT =
    CALCULATE (
        COUNT ( 'Table'[Item No.] ),
        ALLEXCEPT ( 'Table', 'Table'[Item No.] )
    )
RETURN
    IF ( AND ( _COUNT > 1, 'Table'[Status] = "Cancelled" ), "Ignore", "Keep" )

Result in my sample is as below.

Then create a table visual and add this column into filter, select "Keep".

Best Regards,
Rico Zhou

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

[amitchandak]

@saurav_0101 , try

New column =
var _cnt1 = countx(filter(Table, [Item No] = earlier([Item No]) && [Status] = "Cancelled"), [Item No])+0
var _cnt2 = countx(filter(Table, [Item No] = earlier([Item No]) && [Status] <> "Cancelled"), [Item No])+0
result
Switch(True() ,
[Status] <> "Cancelled" , true(),
[Status] = "Cancelled" && _cnt2 = _cnt1 && _cnt1 >0 , false() ,
true() )