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 IDValidityCreditRegion399801/01/2018220HET405001/01/2020117HET405001/01/2020117HET406001/01/202066HET413901/01/202066WAR414501/01/2020389WAR414901/01/2020117HET415725/11/2019117HET416001/01/2020117HET416001/01/2020117HET416401/01/202039WAR416601/01/2020220HET418701/01/2020220HET418701/01/2020220HET418701/01/2020220HET418701/01/2020220HET424801/01/2020429WAR431401/01/2019117HET447401/01/2020444WAR448101/01/202020WAR448701/01/2020435WAR449501/01/202066WAR468501/01/2020410WAR468801/01/2020434WAR469101/01/2020246WAR481310/12/2019515WAR483201/01/2020146WAR499701/01/2020117HET Invoiced amount:Document IDInvoiceReceived3998398906/07/20223998178107/07/20223999451006/07/20224060646705/07/20224139657005/07/20224145443706/07/20224149655405/07/20224150640805/07/20224150704906/07/20224151641105/07/20224152441305/07/20224166437205/07/20224168672905/07/20224169677005/07/20224169654805/07/20224187641405/07/20224248645005/07/20224249654405/07/20224250654605/07/20224481658305/07/20224487661505/07/20224495641905/07/20224685644605/07/20224686654505/07/20224687654305/07/20224931658005/07/20224997661305/07/20224997642105/07/20225139648705/07/20225232646905/07/20225325645105/07/20225418643305/07/20225512641505/07/20225605639805/07/2022

Anonymous

Not applicable

4 years ago

Solved

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

Need Help

PaulDBrown

4 years ago

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

Model Setup.pbix47 KB

8 Replies

parry2k
Super User
4 years ago
Anonymous the best option is to create a separate dimension table for the document numbers which will be a unique value from both the tables and then use this new table to connect to your original tables. This way you will able to visualize the data from both table in the same visual.

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

PaulDBrown

Community Champion

4 years ago

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

Model Setup.pbix47 KB

Anonymous
Not applicable
4 years ago
Hi PaulDBrown Thanks for your reply! I have made the two tables (Credit and Invoice) and added two slicers for the Invoice table.
Is it possible to add the following?
1. The Credit column to the Invoice table (or the Invoice column to the Credit table). I tried it but it is not showing the correct values.
2. A Difference column to show the difference in value for each Document ID between the Credit and Invoice columns.
- grantsamborn
  Solution Sage
  4 years ago
  Instead of using the DocumentID and date from the fact tables, use the corresponding columns from the dimension tables and you should have no problem.

Anonymous

Not applicable

4 years ago

PaulDBrown Is it possible to have an invoice assigned to more than one region? But i'm not sure what changes to make in this case.

I have updated the first table (Total Credit) as below to include more than one region for some of the Document ID's. The second table (Invoiced amount) is unchanged.

Total credit:

Document ID	Validity	Credit	Region
3998	01/01/2018	220	HET
3998	11/05/2018	158	SUP
4050	01/01/2020	117	HET
4050	01/01/2020	117	HET
4050	21/03/2020	256	LRM
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
4160	01/01/2020	96	SUP
4160	08/07/2020	78	LRM
4164	01/01/2020	39	WAR
4166	01/01/2020	220	HET
4166	01/01/2020	144	LRM
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
4487	05/09/2020	725	OTH
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
4997	17/08/2020	961	OTH

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

PaulDBrown
Community Champion
4 years ago
It's better to create a separate dimension table for Region, which is a best practice anyway. So the model has a Date Table, and dimension tables for Document ID and Region.

Create the Document ID table with:

Dim Document ID = DISTINCT ( UNION ( VALUES ( 'Credit Table'[Document ID] ), VALUES ( 'Invoice Table'[Document ID] ) ) )

The region dimension table with:

Dim Region Table = DISTINCT('Credit Table'[Region])

And the model as follows:

Remember to use the fields from the dimension tables in the visuals, slicers, filters, measures...

New file attached
Model Setup1.pbix51 KB

Forum Discussion

How to filter one table by another table

8 Replies

✨ Follow us on LinkedIn and to our YouTube channel

Related content

Filtered table

FILTER TABLE BASED ON ANOTHER TABLE

Filter table using another table result

Conditionally filtering rows from a table with another table

Filtering Dates from another table