New Column: True if Invoice items contain A and B

[adieball]

Hi

excuse the potential stupid title, I couldn't come up with a better explanation.

I have a table containing all items on our invoices, i.e. if a invoice has two line items, i have two rows with identical invoice numbers but different line item details.

I'd like to create a new column (True/False) for the following:

If an Invoice (single invoice number, multiple line items) contains A and B then true

If it contains only A then false

if the logic does not apply (becasue there is no A on the invoice) the "N/A"

I'm not bound to TRUE/FALSE, can be also text like "OK", "Not OK" and "N/A" or whatever is suitable.

I searched the net and asked Aunt Google but couldn't find anything related to this. 

Any help is highly appreciated.

thanks

[v-ljerr-msft · Accepted solution]

Hi @adieball,

According to your description above, you should be able to use the formula below to create the column in your scenario. 

Column =
IF (
    CALCULATE (
        CONTAINS ( 'Table1', 'Table1'[line items], "A" )
            && CONTAINS ( 'Table1', 'Table1'[line items], "B" ),
        ALLEXCEPT ( 'Table1', 'Table1'[invoice number] )
    ),
    "OK",
    IF (
        CALCULATE (
            CONTAINS ( 'Table1', 'Table1'[line items], "A" ),
            ALLEXCEPT ( 'Table1', 'Table1'[invoice number] )
        ),
        "Not OK",
        "N/A"
    )
)

Note: Just replace 'Table1' with your real table name.

Regards

[adieball]

Hi @v-ljerr-msft

thanks a lot. Will try this formula as soon as I can (need todo some other cleanup first).

Andre