Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

How to filter one table by another table

‎07-21-2022 09:07 AM

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:

MakeItReal_0-1658420506774.png

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 IDValidityCreditRegion
399801/01/2018220HET
405001/01/2020117HET
405001/01/2020117HET
406001/01/202066HET
413901/01/202066WAR
414501/01/2020389WAR
414901/01/2020117HET
415725/11/2019117HET
416001/01/2020117HET
416001/01/2020117HET
416401/01/202039WAR
416601/01/2020220HET
418701/01/2020220HET
418701/01/2020220HET
418701/01/2020220HET
418701/01/2020220HET
424801/01/2020429WAR
431401/01/2019117HET
447401/01/2020444WAR
448101/01/202020WAR
448701/01/2020435WAR
449501/01/202066WAR
468501/01/2020410WAR
468801/01/2020434WAR
469101/01/2020246WAR
481310/12/2019515WAR
483201/01/2020146WAR
499701/01/2020117HET

 

Invoiced amount:

Document IDInvoiceReceived
3998398906/07/2022
3998178107/07/2022
3999451006/07/2022
4060646705/07/2022
4139657005/07/2022
4145443706/07/2022
4149655405/07/2022
4150640805/07/2022
4150704906/07/2022
4151641105/07/2022
4152441305/07/2022
4166437205/07/2022
4168672905/07/2022
4169677005/07/2022
4169654805/07/2022
4187641405/07/2022
4248645005/07/2022
4249654405/07/2022
4250654605/07/2022
4481658305/07/2022
4487661505/07/2022
4495641905/07/2022
4685644605/07/2022
4686654505/07/2022
4687654305/07/2022
4931658005/07/2022
4997661305/07/2022
4997642105/07/2022
5139648705/07/2022
5232646905/07/2022
5325645105/07/2022
5418643305/07/2022
5512641505/07/2022
5605639805/07/2022
Labels:
Message 1 of 9
535 Views
0
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

‎07-21-2022 10:01 AM

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] )
    )

Date Table.png

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]
        )
)

Docs ID.png

 

model.pngI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Model Setup.pbix
Message 3 of 9
480 Views
8 REPLIES 8
PaulDBrown
Community Champion
Community Champion

‎07-21-2022 10:01 AM

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] )
    )

Date Table.png

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]
        )
)

Docs ID.png

 

model.pngI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Model Setup.pbix
Message 3 of 9
481 Views
Anonymous
Not applicable
In response to PaulDBrown

‎07-29-2022 02:33 PM

@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 IDValidityCreditRegion
399801/01/2018220HET
399811/05/2018158SUP
405001/01/2020117HET
405001/01/2020117HET
405021/03/2020256LRM
406001/01/202066HET
413901/01/202066WAR
414501/01/2020389WAR
414901/01/2020117HET
415725/11/2019117HET
416001/01/2020117HET
416001/01/2020117HET
416001/01/202096SUP
416008/07/202078LRM
416401/01/202039WAR
416601/01/2020220HET
416601/01/2020144LRM
418701/01/2020220HET
418701/01/2020220HET
418701/01/2020220HET
418701/01/2020220HET
424801/01/2020429WAR
431401/01/2019117HET
447401/01/2020444WAR
448101/01/202020WAR
448701/01/2020435WAR
448705/09/2020725OTH
449501/01/202066WAR
468501/01/2020410WAR
468801/01/2020434WAR
469101/01/2020246WAR
481310/12/2019515WAR
483201/01/2020146WAR
499701/01/2020117HET
499717/08/2020961OTH

 

Invoiced amount:

Document IDInvoiceReceived
3998398906/07/2022
3998178107/07/2022
3999451006/07/2022
4060646705/07/2022
4139657005/07/2022
4145443706/07/2022
4149655405/07/2022
4150640805/07/2022
4150704906/07/2022
4151641105/07/2022
4152441305/07/2022
4166437205/07/2022
4168672905/07/2022
4169677005/07/2022
4169654805/07/2022
4187641405/07/2022
4248645005/07/2022
4249654405/07/2022
4250654605/07/2022
4481658305/07/2022
4487661505/07/2022
4495641905/07/2022
4685644605/07/2022
4686654505/07/2022
4687654305/07/2022
4931658005/07/2022
4997661305/07/2022
4997642105/07/2022
5139648705/07/2022
5232646905/07/2022
5325645105/07/2022
5418643305/07/2022
5512641505/07/2022
5605639805/07/2022
Message 8 of 9
397 Views
0
PaulDBrown
Community Champion
Community Champion
In response to Anonymous

‎07-30-2022 01:30 AM

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:

model.png

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

 

New file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Model Setup1.pbix
Message 9 of 9
387 Views
Anonymous
Not applicable
In response to PaulDBrown

‎07-21-2022 11:32 AM

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.

MakeItReal_0-1658428249525.png

 

Message 4 of 9
472 Views
0
grantsamborn
Solution Sage
Solution Sage
In response to Anonymous

‎07-21-2022 11:56 AM

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.

Message 5 of 9
467 Views
Anonymous
Not applicable
In response to grantsamborn

‎07-21-2022 12:11 PM

@grantsamborn Thanks for your reply! It worked, as shown below. But i am still stuck on the second question about adding a difference column for the Credit - Invoice amount. Any idea how to solve this?

MakeItReal_0-1658430577891.png

 

MakeItReal_1-1658430596188.png

 

Message 6 of 9
460 Views
0
grantsamborn
Solution Sage
Solution Sage
In response to Anonymous

‎07-21-2022 12:25 PM

If I understand correctly, you need 3 measures.

 

Total Credits = SUM('Credit Table'[Credit])
Total Invoices = SUM('Invoice Table'[Invoice])
Difference = [Total Credits] - [Total Invoices]

Message 7 of 9
454 Views
parry2k
Super User
Super User

‎07-21-2022 09:30 AM

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 2 of 9
491 Views

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

View All