Filter Invoices where at least one line item contains a particular product

[cmncp]

I have an invoice table with columns including InvoiceNo and ProductID. I need to be able to filter so that it returns all Invoices that contain a particular Product. When this filter is applied, it needs to only display the invoices with that product, but it should show all products on that invoice.

For example, I have the following table:

InvoiceNo   ProductID
111         ProdA
111         ProdB
111         ProdC
222         ProdA
222         ProdB

I only want to display invoices that have ProdC. When filtered, it needs to show

InvoiceNo   ProductID
111         ProdA
111         ProdB
111         ProdC

I have tried the following DAX Measure:

CALCULATE(
    COUNTROWS(Invoice),
    SUMMARIZE(
        FILTER(
            SUMMARIZE(
                 Invoice
                ,Invoice[InvoiceNo]
                ,Invoice[ProductID]
            )
            ,Invoice[ProductID] = "ProductC"
        )
    ,Invoice[InvoiceNo]
    )
)

But when I apply the filter (where measure > 0), it only returns the following

InvoiceNo   ProductID
111         ProdC

If I was doing this in SQL, I would use the following query:

select 
     [InvoiceNo]
    ,[ProductID]
from
    [Invoice] 
where
    [InvoiceNo] in (SELECT [InvoiceNo] FROM [Invoice] where [ProductID] = 'ProdC')

[Zubair_Muhammad]

@cmncp 

Try this MEASURE and set it as MEASURE =1 in VISUAL FILTER

Measure =
IF (
    CONTAINS (
        CALCULATETABLE (
            VALUES ( Invoice[ProductID] ),
            ALLEXCEPT ( Invoice, Invoice[InvoiceNo] )
        ),
        Invoice[ProductID], "ProdC"
    ),
    1,
    0
)

[Shelley]

Hello @Zubair_Muhammad ,

Your solution here seems pretty elegant. I have a similar use case where I have confirmations with different lines and if any line's description CONTAINS "CBOT" then I want to include all lines for that confirmation in my dataset. 

So here's a data table example:

Confirm Num	Line Item Descr
01	Expense A
01	Expense B
01	CBOT Labor 1
01	CBOT Travel 1
02	Labor for Job
02	Expense for Job
03	Expense 123
03	CBOT Labor Job
03	Expense 456

The expected result:

Confirm #   CBOT?

01                   1

02                   0

03                   1

Here is the expression I am using:

CBOT Confirmations =

IF(

CONTAINS(

CALCULATETABLE(

VALUES('srvc_cnfrmn'[ln_itm_desc]),

ALLEXCEPT('srvc_cnfrmn','srvc_cnfrmn'[cnfrmn_num])

),

'srvc_cnfrmn'[ln_itm_desc],"CBOT"

),

1,

0

)

However, I am receiving an error: The expression specified in the query is not a valid table expression. Do you know why? Is it because I am trying to search for a string within a larger string? Do you have any ideas how to resolve? Thanks for your help.

[Shelley]

@Zubair_Muhammad I figured out what I was doing wrong. When I saw CALCULATETABLE, I thought I had to add a table, but this is a measure. Once I did it as a measure, it worked. Thank you!

[cmncp]

Thanks @Zubair_Muhammad 

This is close, but if I include Product in my table, it will list every product in the product table, not just those on the invoice.  Eg.  I am expecting to see this (NewMeasure is the measure you defined):

InvoiceNo   ProductID    NewMeasure
111            ProdA         1
111            ProdB         1
111            ProdC         1

But instead I am seeing this:

InvoiceNo   ProductID    NewMeasure
111            ProdA         1
111            ProdB         1
111            ProdC         1
111            ProdD         1
111            ProdE         1
111            ProdF         1
111            ProdG         1
111            ProdH         1

Even though the invoice only has ProdA, B and C on it.

[Zubair_Muhammad · Accepted solution]

@cmncp 

It works with me

I am attaching a sample file with your data and MEASURE