Forum Discussion
Invoice-lines
I'm new to PowerBI, so any help would be greatly appreciated! I have attached a table below as an example.
| Invoice ID | Invoice line ID | Category | Wanted |
| 1 | 1 | A | A |
| 1 | 2 | B | A |
| 1 | 3 | A | A |
| 1 | 4 | A | A |
| 2 | 5 | A | A |
| 2 | 6 | B | A |
| 2 | 7 | C | A |
| 3 | 8 | B | Not A |
| 3 | 9 | B | Not A |
| 4 | 10 | B | Not A |
| 4 | 11 | B | Not A |
| 4 | 12 | C | Not A |
| 4 | 13 | C | Not A |
I have some invoices which need to be categorized. The "Wanted" column contains an example of the required output.
In the table, there are 4 unique invoices (4 unique IDs). An invoice consists of multiple invoice lines (13 unique IDs). For example, Invoice ID 2 contains 3 Invoice lines, with Invoice line ID 5, 6 and 7. For each Invoice ID, I would like to check whether one or multiples rows contain Category "A". If that is the case, the output should be "A", otherwise it should be "Not A".
For example, Invoice ID 1 contains three rows with Category "A", so the wanted output should be "A". Invoice ID 2 contains one row with Category "A", so the output should also be "A". Invoice ID 3 and 4 contain no row with Category "A", so the output should be "Not A".
Thanks in advance!
Anonymous
You can add a calculated column to your table with this code.
Wanted = VAR _ACount = CALCULATE( COUNTROWS('Table'), 'Table'[Category]="A" ,ALLEXCEPT('Table','Table'[Invoice ID]) ) RETURN IF ( _ACount > 0, "A", "Not A")
2 Replies
Anonymous
You can add a calculated column to your table with this code.
Wanted = VAR _ACount = CALCULATE( COUNTROWS('Table'), 'Table'[Category]="A" ,ALLEXCEPT('Table','Table'[Invoice ID]) ) RETURN IF ( _ACount > 0, "A", "Not A")