cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cmncp Regular Visitor
Regular Visitor

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

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')
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@cmncp 

 

It works with me

I am attaching a sample file with your data and MEASURE

View solution in original post

3 REPLIES 3
Super User
Super User

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

@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
)
cmncp Regular Visitor
Regular Visitor

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

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.

Super User
Super User

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

@cmncp 

 

It works with me

I am attaching a sample file with your data and MEASURE

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 238 members 2,426 guests
Please welcome our newest community members: