Forum Discussion
Filtering multiple instances
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 |
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 )
4 Replies
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 )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] ) )
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.
