Filtering a table on multiple columns and rows - more efficient solution needed

[hanssonnor]

Hi,

I am looking for a more efficient solution. I have a table which contains all invoice numbers.

Besides the invoice number the table contains as well three additional columns

Case Number: 

- NR-Number: Issues with the invoice

- Protocol-Number: Blocked by Person Number

For each NR-Number/Protocol there are always two rows:

- X = Started

- T or W, T = Rejected or W = confirmed

I tried to create multiple calculated columns with true and false in order to filter my information.

The information I want to obtain are all "active" NR-Numbers & Protocols, hence those which don't have a " T" or "W" booking.

For one invoice number, you can always have multiple NR-Numbers and Protocol-Numbers.

Maybe you have a better solution for me?

Enclosed the table with some examples:

Invoice	Case Number	Solution	Description
40111469	NR-0151033	X	Proposal
40111469	NR-0151033	W	Completed
40112599	VLF20-00079	X	Proposal
40112599	VLF20-00079	W	Completed
40112722	VLF20-00079	X	Proposal
40112722	VLF20-00079	W	Completed
40114265	VLF20-00079	X	Proposal
40114265	VLF20-00079	T	Completed
40117600	NR-0152037	X	Proposal
40117600	NR-0152037	T	Completed
40118401	VLF20-00079	X	Proposal
40118401	VLF20-00079	W	Completed
40119056	NR-0152036	X	Proposal
40119056	NR-0152036	W	Completed
40119772	NR-0146105	X	Proposal
40119772	NR-0146105	T	Completed
40120143	NR-0146105	X	Proposal
40120143	NR-0146105	T	Completed
40120343	VLF20-00076	X	Proposal
40120343	VLF20-00076	T	Completed
40125436	NR-0153026	X	Proposal
40125506	NR-0153331	X	Proposal
40125513	NR-0153331	X	Proposal
40125515	NR-0153331	X	Proposal
40125524	NR-0153331	X	Proposal
40126650	NR-0146968	X	Proposal
40126650	VLF20-00061	X	Proposal
40126688	NR-0151033	X	Proposal
40126732	VLF20-00090	X	Proposal
40126733	VLF20-00090	X	Proposal

The table is linked with my "Master" table via the invoice number which is unique in the "Master" table.

Besides knowing which invoice still has active NR or Protocol-Numbers, I want to be able to create an overview of all active NR / Protocol Numbers which are currently blocking invoices.

Thanks in advance for your hints.

Best regards

Hansson

[hanssonnor]

@amitchandak , thanks for your quick reply.

The measure works very well, and it is far easier to create an overview compared to my true & false filters.

[amitchandak · Accepted solution]

@hanssonnor , Try a measure like this.

This should give not in "T","W"

countx(filter(summarize(Table, Table[Invoice],Table[Case Number], "_1", calculate(count(Table[Solution]),table[Solution] in {"T","W"})+0),[_1]=0),[Invoice])