Forum Discussion
Same table filtering 2 different columns
Dear all,
I'm wondering if there is a way of doing this.
I have a table of Receivables where we register when we send an invoice to our customers:
| EntryNo | Document | Amount | InvoiceNr |
| 1989 | PG1558 | 100 | |
| 1990 | BZ1559 | 50 | |
| 1991 | AE1300 | 65 |
In the same table, when the amount is paid, there will be registered a new entry and the values in the Column "Document" will be registered in the column "InvoiceNr" like this:
| EntryNo | Document | Amount | InvoiceNr |
| 2060 | -100 | PG1558 | |
| 2061 | -50 | BZ1559 | |
| 2062 | -65 | AE1300 |
I want to have in a dashboard 2 tables, one for the invoices (the one above whith data in "Document") and one for payments (the below one with data in "InvoiceNr"
What I need is to be able to click on an "Document" from the above and get the line for the below table filtered by the same value but on the column "InvoiceNr".
Is it possible to do this whithout having to duplicate the whole receivables table and make a relationship between the 2 columns?
Thank you in advance!
setis You have done everything correct but in the table visual (for Receivables) use the Document from your unique list that was created in your relationship "RecDocNrs" then it should work.
6 Replies
setis I think of two options for this.
1. Separate the Receivables data and Paid data into two different tables and link them (Note - we are not duplicating complete table just splittng them up)
2. Create a calculated column as below
Doc = IF(LEN(Test203SelfJoin[Document])=0,Test203SelfJoin[InvoiceNr],Test203SelfJoin[Document])
Use two Table visuals one for Receivables and Paid. Make a Visual level filter for each table (InvoiceNr IS BLANK for Receivables and Document IS BLANK for Paid visual). Here I've used an another calculated column to make it more clear to flag Receivables and Paid, used that in the visual filter.
Flag = IF(LEN(Test203SelfJoin[InvoiceNr])=0,"Receivables","Paid")
Use the Doc field (calculated column) as Filter
Dear PattemManohar
Thank you very much for your answer but it didn't worked for me. I would like to try to avoid using a slicer if possible. I would like to select a line on the above table with a "Document" and get the line (or lines) with the matched "Invoice Nr."
I know that it isn't the best idea but I actually tested duplicating he Receivables table. Since the relationship between "Document No." and "InvoiceNr" is many to many, I extracted the columns of "Document No", eliminated duplicates and blanks and created a relationship between the 2 tables as per below:
The thing is that it looks like the relationship is not working.
When I create 2 tables from "Receivables" and "RecPayments" and select a line from the first one, the second one doesn't change.
It does work if I create a slicer from "RecDocNrs".
Is there any other approach I can use to connect these 2 tables?
setis Could you please confirm whether DocumentNo in Receivables is unique or will contain duplicate entries with different EntryNo.
