Filter with multiple criteria

[PBI_newuser]

Hi,

If i want to create a new field in order for me to filter out only those ID with Cancelled orders.

How to do that? Below is the example.

E.g.

A-1 has one active order and one cancelled order. I do not want it to show up in the table.

A-2 has only one cancelled order, I want it to show up in the table.

A-3 has all cancelled orders, I want it to show up in the table.

ID	Order#	Status
A-1	PO-111	Cancelled
A-1	PO-111	Active
A-2	PO-333	Cancelled
A-3	PO-222	Cancelled
A-3	PO-444	Cancelled

[jaideepnema · Accepted solution]

Hi @PBI_newuser ,

Create a calculated column as shown below:

Status Flag = IF('Table'[Status]="Active",1,0)

And create a measure as shown below:

Status Filter = CALCULATE(SUM('Table'[Status Flag]),ALLEXCEPT('Table','Table'[ID]))

Then apply the status filter measure as a visual level filter:

 this will give you the required result:

In case you need the PBIX here is the link:

https://we.tl/t-EEPKZfRCrn

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[PBI_newuser]

Hi @jaideepnema , it works! Thank you!

[jaideepnema]

Hi @PBI_newuser ,

Not very clear but do you want to show this in a table or any other visual or want to create a new column ?

[PBI_newuser]

Hi @jaideepnema ,  I would like to show in Table visual with only all cancelled IDs.