Forum Discussion

hanssonnor's avatar
hanssonnor
Frequent Visitor
5 years ago
Solved

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

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:

 

InvoiceCase NumberSolutionDescription
40111469NR-0151033XProposal
40111469NR-0151033WCompleted
40112599VLF20-00079XProposal
40112599VLF20-00079WCompleted
40112722VLF20-00079XProposal
40112722VLF20-00079WCompleted
40114265VLF20-00079XProposal
40114265VLF20-00079TCompleted
40117600NR-0152037XProposal
40117600NR-0152037TCompleted
40118401VLF20-00079XProposal
40118401VLF20-00079WCompleted
40119056NR-0152036XProposal
40119056NR-0152036WCompleted
40119772NR-0146105XProposal
40119772NR-0146105TCompleted
40120143NR-0146105XProposal
40120143NR-0146105TCompleted
40120343VLF20-00076XProposal
40120343VLF20-00076TCompleted
40125436NR-0153026XProposal
40125506NR-0153331XProposal
40125513NR-0153331XProposal
40125515NR-0153331XProposal
40125524NR-0153331XProposal
40126650NR-0146968XProposal
40126650VLF20-00061XProposal
40126688NR-0151033XProposal
40126732VLF20-00090XProposal
40126733VLF20-00090XProposal

 

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

  • amitchandak's avatar
    amitchandak
    5 years ago

    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])

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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])

     

  • hanssonnor's avatar
    hanssonnor
    Frequent Visitor
    5 years ago

    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.