Drill through option not visible

[ldwf]

I am trying to do a simple drill through between a summary and detail table that are linked together by a ID field.  Something like this image, although the id is made up of a concatenation of four fields that are common between the summary and detail tables:

When I join the two  tables by the ID, the model makes it a many-many join.  I build the summary matrix on one page  with no problem, and the detail table I want to drill to on another page with no issue.  On the detail page, I add the ID from the summary table to the drill-through field list and I enable the 'keep all filters' option.  I want to drill through from the summary matrix but I don't want to make the ID visible; I just want to drill thru from the values and have it pass the ID value.  But when I go to the page with the summary table and right click on any of the values in the matrix, the option to drill through doesn't show up. Is the problem the many-many join in the model?  Do I need to build abridge table? 

[AnalyticsWizard]

@ldwf 

In Power BI, a drill-through requires a relationship between the tables that is one-to-many or one-to-one, where one side is unique. It's not possible to set up a drill-through using a many-to-many relationship directly because Power BI wouldn't know how to filter the detail page correctly without a unique identifier. Here's how you can approach this:

1. Unique Identifier: Make sure that the concatenation of the four fields that create the ID results in a unique key in the summary table. This is crucial for the drill-through to work correctly.

2. Relationship: Establish a one-to-many relationship from the summary table to the detail table using this unique ID. If your summary table doesn't have unique values for the ID field, consider creating a bridge table that does.

3. Bridge Table: If necessary, create a bridge table that has unique IDs and is related to both the summary and detail tables. This bridge table will facilitate the drill-through.

4. Drill-through Setup: On the detail page, add the unique ID from the summary or bridge table to the drill-through filters. Then, in the matrix on the summary page, you don't have to make the ID visible. Instead, you just need to ensure that the ID column from the summary table (or bridge table) is in the Values area of the matrix visual.

5. Enable Drill-through: Make sure you're right-clicking on the actual data value within the matrix, not the headers or totals. The drill-through option should appear when you right-click on a value if the setup is correct.

6. Drill-through Option: If the drill-through option is still not showing up, check the drill-through filters on the target page to make sure that they are set up correctly with the ID from the summary table.

7. Many-to-Many Relationships: If you must use a many-to-many relationship, which isn't recommended for drill-through scenarios, ensure that the model is correctly filtering down to unique values for the drill-through to work.

By ensuring there's a unique ID to link the summary and detail data and correctly setting up the relationship and drill-through feature, you should be able to drill from the summary matrix to the details page without showing the ID in the matrix.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[ldwf · Accepted solution]

The many-many join is happening because they loaded dupes into the summary table.  That is resolved now, and my join is a one-many.  

[tharunkumarRTK]

@ldwf 

In the summary table if you do not want to make the ID visible then you play with formatting and make it hidden. Like for example if your visual background is white and font color is black then make the font clolor fot ID column values and header as white. This will solve the problem.

There are few other options like visual calcuations using which you can hide.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun