Forum Discussion
Create a filter on a table from another filtered table without relations
I have two tables without a relationship between them:
- Customer - contains items from the financial system with invoices, credit notes, payments and more.
- Orders - contains sales orders and credit notes from the sales order system
On two different pages in a Power BI file I have made a Matrix visualization with the debtor records (page 1) and in another visual I have a Matrix with sales orders (page 2). When I select an post with the type invoice or credit note on page 1, I would like to be able to make a filter that I can transfer to the second Matrix visualization with the orders on page 2 and here view the selcted order on page 1.
Page 1 Page 2
I would have liked to be able to use Drill Through from Customer table to Order table, but this is probably not possible when I have no relationship between the two tables.
I would therefore like to hear if anyone knows of a solution to this?
I have been thinking about whether it could be possible on page 2 to make a filtering of the Order table based on the active filter on the Customer table. This could be done by first finding out if the selected item on the Customer table on page 1 has a Type equal to Invoice or Credit Memo. If so, the current value in Doc.No without hyphens and letters must be used to insert as a filter on the Order table for the Order.No field. Then it must be possible to jump from page 1 to page 2 where rows with the current Order.No will be displayed in the Matrix for the Order Table.
Hi Lars_Denmark ,
If i understand you correctly, you could create measures use SELECTEDVALUE() function or USERELATIONSHIP() function to make a connection between two tables.
https://docs.microsoft.com/en-us/dax/selectedvalue-function
https://docs.microsoft.com/en-us/dax/userelationship-function-dax
Then create a sycn slicer between these pages.
Best Regards,
Jay
Best Regards,
Jay
5 Replies
Hi Lars_Denmark ,
Is there a reason that you don't want to create any relationship between these 2 tables?
Thanks,
Pragati
Thank you for your interest in finding a solution to this.
I do not have a relationship between the two tables, as there are records in the Debtor table with a Document_No, which does not exist in the Order table with the same No. For example, payment items with a document number that are not in the Order table.
I have tried to make a relation from the Debtor table to the Order table, but here a problem arises, which you can see below, where the relation is only dashed:
Lars_Denmark Since, there is no active reationship between two tables. You can use the inactive relationship to create measures and then use them in your visual. Refer to below links in using inactive relationship in measures.
https://docs.microsoft.com/en-us/dax/userelationship-function-dax
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
The easiest (and recommended way) is to have Dimension tables (Customer, Type etc) linked to your tables in one-to-many relationships and use the fields from these dimension tables in the visuals. You can then use Interactions or drillthrough functions to select rows you wish to see in the second table.
An alternative is to write measure using the TREATAS function , which establishes a virtual one-to-one/many relationships between corresponding fields in unrelated tables, but this is much less "efficient" than using dimension tables (by a long way).
Hi Lars_Denmark ,
If i understand you correctly, you could create measures use SELECTEDVALUE() function or USERELATIONSHIP() function to make a connection between two tables.
https://docs.microsoft.com/en-us/dax/selectedvalue-function
https://docs.microsoft.com/en-us/dax/userelationship-function-dax
Then create a sycn slicer between these pages.
Best Regards,
Jay
Best Regards,
Jay
