Help with identifying what else is on an invoice with a specific item

[Hoggy77]

I have a data set of invoices and invoice line items. These line items have a related category and sub category for several other tables. 

I need to identify all "invoice_no" with items in "itemsubcategory" = "Consult" and then see if that consult was converted and they purchased other items 

I have tried Power Query but as the sub category comes from multiple different tables depending if the item is a product, service or package it got too messy. 

Please Help! 

[Anonymous · Accepted solution]

Hi @Hoggy77 ,

You can try below formula:

AdditionalItemsPurchased =
CALCULATE (
    COUNTROWS ( 'LineItems' ),
    'LineItems'[invoice_no] IN VALUES ( Invoices[invoice_no] ),
    'LineItems'[itemsubcategory] <> "Consult"
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hoggy77 , Try a measure like

Measure =

var _inv = summarize(calculatetable(Invoice, Item[itemsubcategory]= "Consult"), Invoice[Invoice No])

return

calculate(COuntrows(Invoice), Filter(Invoice, not(isblank(invoice[Purchase Date]))  && Invoice[Invoice No] in _inv))

The purchase date condition needs to improve