Forum Discussion
Drill-through with custom field
Hi guys, I am totally new to PowerBI. Here is my quesiton.
I have a matrix table with item code and whs name in a page. This is Page1. Item code and whs name make it the primay key.
Also a detail page which will show SAP PO list with item information. This is Page2.
So what I want is how to drill-through to Page2 with the same item list when you right-click the item code in Page1?
I cannot build a relationship with item code because code is not unique in both tables, they are many-to-many relationship.
Any idea would be appreciate, thanks all!
Hi, Ben_Jiang
Since you didn't give test data for testing, I created two simple tables myself:
Page1:
Page2:
As 123abc said, since the relationship is many-to-many, you can create a bridge table:BridgeTable = DISTINCT ( UNION ( VALUES ( 'Page1'[ItemCode] ), VALUES ( Page2[ItemCode] ) ) )
Then create a one-to-many relationship with Page1 and Page2 by splitting the bridged table into two tables and also changing the Cross-filter direction to Both:Then drag the ItemCode of Page2 to the drill-through fields of Page1,drag the ItemCode of Page1 to the drill-through fields of Page2:
Result:
I have attached the pbix file for this simple example below, hope it helps!I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
- 123abc
Community Champion
Here’s a step-by-step guide:
Create a Drill-Through Page:
- Go to Page2 where you want to show the detailed SAP PO list.
- Add the fields you want to display in the drill-through.
Add a Drill-Through Field:
- On Page2, drag the Item Code field into the Drill-through filters well.
- This will enable the drill-through functionality for Item Code.
Set Up the Matrix Table:
- On Page1, ensure your matrix table includes Item Code and Whs Name.
Enable Drill-Through:
- Right-click on the Item Code in the matrix table on Page1.
- You should see an option to drill through to Page2.
Handling Many-to-Many Relationships:
- Since you mentioned that Item Code is not unique, you can use a bridge table to handle the many-to-many relationship.
- Create a new table that contains unique Item Codes.
- Establish one-to-many relationships between this bridge table and your existing tables.
Using the Bridge Table:
- Use the Item Code from the bridge table in your matrix and detail pages.
- This will ensure that the drill-through works correctly.
By following these steps, you should be able to drill through from Page1 to Page2 with the same item list. If you have any more questions or need further clarification, feel free to ask!
Hi, could you explain more detail on step 5. In fact, I dont think there's a strong relation between the two datasets. All what I want to do is try to drill through to Page2's item list by the item code from Page1.
Is it necessary to establish table relationships?
- 123abc
Can you please share your sample PBIX file. i will provide you ready to cook soluiton.
Hi, Ben_Jiang
Since you didn't give test data for testing, I created two simple tables myself:
Page1:
Page2:
As 123abc said, since the relationship is many-to-many, you can create a bridge table:BridgeTable = DISTINCT ( UNION ( VALUES ( 'Page1'[ItemCode] ), VALUES ( Page2[ItemCode] ) ) )
Then create a one-to-many relationship with Page1 and Page2 by splitting the bridged table into two tables and also changing the Cross-filter direction to Both:Then drag the ItemCode of Page2 to the drill-through fields of Page1,drag the ItemCode of Page1 to the drill-through fields of Page2:
Result:
I have attached the pbix file for this simple example below, hope it helps!I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for the detail explaination. It works now.
Also I have another question, let's say if I have Page3 or Page4 created from the Page1,just did some row filtering. So if I wanna drill through to Page2 from Page3 and Page4, which table should I create the relationship with the bridge table? Page1? Or both on Page3 and Page4?
Hi, ssab_wenx
If your Page1 has created a relationship with the bridge table, and Page3 or Page4 has also created a relationship with Page1, Page3 and Page4 do not need to create a relationship with the bridge table.
However, if your Page3 and Page4 do not have a relationship with Page1, Page3 and Page4 need to create a relationship with the bridging table.
Note that you need to change cross-filter-direction to Both.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
