Invoice-lines

[Anonymous]

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!

[jdbuchanan71 · Accepted solution]

@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")

[Anonymous]

It works perfectly. Your help is greatly appreciated, you saved me a lot of work. Thanks a lot!