Forum Discussion
Create new table with: ADDCOLUMN, SELECTCOLUMN, FILTER
Hi all,
I have been trying without success to create a new table that 1st adds 2 columns from 2 related tables, then selects some of the columns from the resulting table, and then Filters. I explain the situation with examples:
Table 1 - Invoices
| Invoice ID | Customer ID | Invoice Date |
| 352 | 100 | 01/01/2023 |
| 353 | 100 | 05/15/2023 |
| 354 | 101 | 02/01/2023 |
| 355 | 101 | 06/15/2023 |
Table 2 - Items
| Item ID | Item Name |
| 200 | Chocolate |
| 201 | Vanilla |
Table 3 - Invoice lines
| Invoice ID | Item ID | Quantity | SalesAmount | Extra column |
| 352 | 200 | 10 | 100 | Data-x |
| 352 | 201 | 20 | 200 | Data-y |
| 353 | 200 | 5 | 50 | Data-z |
| 353 | 201 | 10 | 100 | Data-a |
| 354 | 200 | 20 | 200 | Data-b |
| 355 | 200 | 30 | 300 | Data-c |
Relations
1 to many --> Invoices[Invoice ID] to 'Invoice lines'[Invoice ID]
1 to many --> Items[Item ID] to 'Invoice lines'[Item ID]
Expected result
What I need is a table that:
- 1st adds the columns [Customer ID] and [Item ID]
- 2nd removes [Extra column]
- 3rd filters the table to only include the latest invoice by customer (calculated eiather using [Invoice ID] or [Date])
| Invoice ID | Customer ID | Invoice Date | Item ID | Item Name | Quantity | Sale Amount |
| 353 | 100 | 05/15/2023 | 200 | Chocolate | 5 | 50 |
| 353 | 100 | 05/15/2023 | 201 | Vanilla | 10 | 100 |
| 355 | 101 | 06/15/2023 | 200 | Chocolate | 30 | 300 |
Thank you,
JIRAMON
Leverage physic relationships for better performance,
Latest = ADDCOLUMNS( CALCULATETABLE( 'Invoice Lines', FILTER( Invoices, Invoices[Invoice Date] = CALCULATE( MAX( Invoices[Invoice Date] ), ALLEXCEPT( Invoices, Invoices[Customer ID] ) ) ) ), "Cust ID", RELATED( Invoices[Customer ID] ), "Inv Date", RELATED( Invoices[Invoice Date] ), "Item Name", RELATED( Items[Item Name] ) )
3 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but pleas try something like below.
Please check the below picture and the attached pbix file.
Expected result table = VAR _latestinvoicebycustomer = GROUPBY ( Invoices, Invoices[Customer ID], "@lastestinvoice", MAXX ( CURRENTGROUP (), Invoices[Invoice ID] ) ) RETURN CALCULATETABLE ( SUMMARIZE ( 'Invoice lines', Invoices[Invoice ID], Invoices[Customer ID], Invoices[Invoice Date], Items[Item ID], Items[Item Name], 'Invoice lines'[Quantity], 'Invoice lines'[SalesAmount] ), TREATAS ( _latestinvoicebycustomer, Invoices[Customer ID], Invoices[Invoice ID] ) ) - ThxAlot
Leverage physic relationships for better performance,
Latest = ADDCOLUMNS( CALCULATETABLE( 'Invoice Lines', FILTER( Invoices, Invoices[Invoice Date] = CALCULATE( MAX( Invoices[Invoice Date] ), ALLEXCEPT( Invoices, Invoices[Customer ID] ) ) ) ), "Cust ID", RELATED( Invoices[Customer ID] ), "Inv Date", RELATED( Invoices[Invoice Date] ), "Item Name", RELATED( Items[Item Name] ) ) Thank you both for the proposed solutions. Both worked great!
