Forum Discussion
Post PartisanSame table filtering 2 different columns
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.
Community Championsetis 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
setisDear 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?
- PattemManohar
setis Could you please confirm whether DocumentNo in Receivables is unique or will contain duplicate entries with different EntryNo.
- setis
Dear PattemManohar It has duplicates with different entry numbers