Forum Discussion
How to filter one table by another table
Hello,
I have the following two tables, named "Total credit" and "Invoiced amount". I am trying to find a way to filter the second table, "Invoiced amount" by Region.
Since the "Document ID" column is common to both tables (however, not all Document ID numbers are found in both tables and they are not always unique), i tried to create a relationship between the two tables in the Model window of Power BI:
I then added the second table to a Table visualization in Power BI and added a slicer to filter by Region but it does not work.
The Document ID from the first table should be considered as the reference list, so any Document ID's not in common with the second table should be ignored.
Any help is much appreciated!
Total credit:
| Document ID | Validity | Credit | Region |
| 3998 | 01/01/2018 | 220 | HET |
| 4050 | 01/01/2020 | 117 | HET |
| 4050 | 01/01/2020 | 117 | HET |
| 4060 | 01/01/2020 | 66 | HET |
| 4139 | 01/01/2020 | 66 | WAR |
| 4145 | 01/01/2020 | 389 | WAR |
| 4149 | 01/01/2020 | 117 | HET |
| 4157 | 25/11/2019 | 117 | HET |
| 4160 | 01/01/2020 | 117 | HET |
| 4160 | 01/01/2020 | 117 | HET |
| 4164 | 01/01/2020 | 39 | WAR |
| 4166 | 01/01/2020 | 220 | HET |
| 4187 | 01/01/2020 | 220 | HET |
| 4187 | 01/01/2020 | 220 | HET |
| 4187 | 01/01/2020 | 220 | HET |
| 4187 | 01/01/2020 | 220 | HET |
| 4248 | 01/01/2020 | 429 | WAR |
| 4314 | 01/01/2019 | 117 | HET |
| 4474 | 01/01/2020 | 444 | WAR |
| 4481 | 01/01/2020 | 20 | WAR |
| 4487 | 01/01/2020 | 435 | WAR |
| 4495 | 01/01/2020 | 66 | WAR |
| 4685 | 01/01/2020 | 410 | WAR |
| 4688 | 01/01/2020 | 434 | WAR |
| 4691 | 01/01/2020 | 246 | WAR |
| 4813 | 10/12/2019 | 515 | WAR |
| 4832 | 01/01/2020 | 146 | WAR |
| 4997 | 01/01/2020 | 117 | HET |
Invoiced amount:
| Document ID | Invoice | Received |
| 3998 | 3989 | 06/07/2022 |
| 3998 | 1781 | 07/07/2022 |
| 3999 | 4510 | 06/07/2022 |
| 4060 | 6467 | 05/07/2022 |
| 4139 | 6570 | 05/07/2022 |
| 4145 | 4437 | 06/07/2022 |
| 4149 | 6554 | 05/07/2022 |
| 4150 | 6408 | 05/07/2022 |
| 4150 | 7049 | 06/07/2022 |
| 4151 | 6411 | 05/07/2022 |
| 4152 | 4413 | 05/07/2022 |
| 4166 | 4372 | 05/07/2022 |
| 4168 | 6729 | 05/07/2022 |
| 4169 | 6770 | 05/07/2022 |
| 4169 | 6548 | 05/07/2022 |
| 4187 | 6414 | 05/07/2022 |
| 4248 | 6450 | 05/07/2022 |
| 4249 | 6544 | 05/07/2022 |
| 4250 | 6546 | 05/07/2022 |
| 4481 | 6583 | 05/07/2022 |
| 4487 | 6615 | 05/07/2022 |
| 4495 | 6419 | 05/07/2022 |
| 4685 | 6446 | 05/07/2022 |
| 4686 | 6545 | 05/07/2022 |
| 4687 | 6543 | 05/07/2022 |
| 4931 | 6580 | 05/07/2022 |
| 4997 | 6613 | 05/07/2022 |
| 4997 | 6421 | 05/07/2022 |
| 5139 | 6487 | 05/07/2022 |
| 5232 | 6469 | 05/07/2022 |
| 5325 | 6451 | 05/07/2022 |
| 5418 | 6433 | 05/07/2022 |
| 5512 | 6415 | 05/07/2022 |
| 5605 | 6398 | 05/07/2022 |
Assuming that an invoice is assigned to a single region, I suggest you create a date table and a dimension table for documents & region to use in slicers, filters, measures, visuals:
Date Table = VAR _CreditDates = SELECTCOLUMNS ( 'Credit Table', "@Date", 'Credit Table'[Validity] ) VAR _InvoiceDates = SELECTCOLUMNS ( 'Invoice Table', "@Dates", 'Invoice Table'[Received] ) VAR _ListDates = DISTINCT ( UNION ( _CreditDates, _InvoiceDates ) ) VAR _MinDate = MINX ( _ListDates, [@Date] ) VAR _MaxDate = MAXX ( _ListDates, [@Date] ) RETURN ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), "MonthNum", MONTH ( [Date] ), "Month", FORMAT ( [Date], "MMMM" ), "Year", YEAR ( [Date] ) )Dim Document ID = ADDCOLUMNS ( DISTINCT ( UNION ( VALUES ( 'Credit Table'[Document ID] ), VALUES ( 'Invoice Table'[Document ID] ) ) ), "Region", LOOKUPVALUE ( 'Credit Table'[Region], 'Credit Table'[Document ID], 'Credit Table'[Document ID] ) )I've attached the sample PBIX file
