Filtering multiple instances

[Ramps]

Salesfile contains the fields Invoice Ref, Date, Week, Customer, Product & Quantity. (To keep things simple in this example the invoice line is unique for each row).

How can the invoice lines be listed in a Power BI Table (not Excel) just for products with more than 1 invoice in any week?

See example of Salesfile and the Desired Report. 

Note the Radio product only had one invoice in week 5 but 2 in week 6

Many thanks in advance for sharing hints and tips.

Example Salesfiles:-

Product	Week	Customer	Quantity	Date	Invoice
Car	4	John	25	26-Jan-18	5894
Kettle	2	Mary	16	11-Jan-18	9864
Laptop	2	Gill	28	11-Jan-18	5678
Phone	3	Paul	8	14-Jan-18	3256
Phone	3	Kath	17	14-Jan-18	3256
Radio	5	John	34	30-Jan-18	7654
Radio	6	Gill	31	05-Feb-18	8786
Radio	6	Mary	19	10-Feb-18	4568
TV	1	Mary	28	01-Jan-18	1234
TV	1	Fred	14	05-Jan-18	2267
Washing Machine	3	John	19	16-Jan-18	1567

Desired report:-

Product	Week	Customer	Quantity	Date	Invoice
Phone	3	Paul	8	14-Jan-18	3256
Phone	3	Kath	17	14-Jan-18	3256
Radio	6	Gill	31	05-Feb-18	8786
Radio	6	Mary	19	10-Feb-18	4568
TV	1	Mary	28	01-Jan-18	1234
TV	1	Fred	14	05-Jan-18	2267

[Zubair_Muhammad · Accepted solution]

Hi @Ramps

One way is to create a CALCULATED TABLE....

From the Modelling Tab >>> NEW TABLE

Table =
VAR supportingtable =
    ADDCOLUMNS (
        SalesFile,
        "Morethan1Invoice", CALCULATE (
            COUNT ( SalesFile[Invoice] ),
            ALLEXCEPT ( SalesFile, SalesFile[Product], SalesFile[Week] )
        )
    )
RETURN
    FILTER ( supportingtable, [Morethan1Invoice] > 1 )

[Ramps]

Thank you Zubair_Muhammad for helping me twice this week with hints and tips.

I got your well explained solution to work OK, thank you, and I will try the 2nd suggestion  later.

[Zubair_Muhammad]

@Ramps

[Zubair_Muhammad]

@Ramps

Or you can add a calculated column and use it to filter the table

Column =
CALCULATE (
    COUNT ( SalesFile[Invoice] ),
    ALLEXCEPT ( SalesFile, SalesFile[Product], SalesFile[Week] )
)