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.
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
Solved! Go to 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])
@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.
@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])
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |