Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@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() )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |