Forum Discussion
Drill through not filtering to specific values
I have a drill thru on the value "Chartstring_Full" that goes from a table in one page to a table in another. I can right-click the value in the first table and go to the drill-thru table, but instead of filtering for the specific corresponding values it returns to me every value in the table. I have the "Chartstring_Full" from the first table as my filter for the Drill-Through page, and am using the "Chartstring_Full" from the corresponding table as the value for my drill-through table. Does anyone know what may be going on? Any help would be greatly appreciated.
This is the first table, the "COLUM-xxxx" value is the "Chartstring_Full" I'm trying to drill though on.
This is the second drill-through table. Instead of filtering out the rows with the specific "Chartstring_Full" that corresponds to the first table, it returns all rows to me.
Hi SteD, drillthroughs are not set from table to table. You set drillthrough fields on the page properties where the second table lives.
And, as GeraldGEmerick, asked, is the Chartstring_Full column in both tables and do they have an active relationship from one to another.
Extra tip: if the Chartstring_Full column is a key column, it can use a lot of memory in your semantic model when you have a lot of rows using that key. You might want to consider generating a unique number from the string or add an index column instead using Power Query M and use those as a key for relationships.
Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.
Hi SteD
For drillthroughs to work, the column used in the target page must be the same column from the originating visual. For example, CalendarTable[date] --> FactTable[Date], if CalendarTable[date] is used in the originating visual, the same column must be used in the target page. It isn't enough that there is an existing relationship between the two.
7 Replies
- GeraldGEmerick
Memorable Member
SteD Is it possible that there is a missing relationship between two tables?
- nielsvdc
Super User
Hi SteD, drillthroughs are not set from table to table. You set drillthrough fields on the page properties where the second table lives.
And, as GeraldGEmerick, asked, is the Chartstring_Full column in both tables and do they have an active relationship from one to another.
Extra tip: if the Chartstring_Full column is a key column, it can use a lot of memory in your semantic model when you have a lot of rows using that key. You might want to consider generating a unique number from the string or add an index column instead using Power Query M and use those as a key for relationships.
Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.
Thank you! I added a relationship between them. The relationship is many-to-many so it required a bridge table of Chartstring_Fulls, but PowerBI won't let me set up a many-to-one bi-directional relationship between the two tables to the bridge table, and thus I still can't get the drill through to work. Any advice here?
- nielsvdc
It depends on the error message you are getting, trying to activate the bi-directional relationships. And, of course, make sure your bridge table of Chartstring_Fulls has no duplicates.
Best practices recommend minimizing bi-directional relationships whenever possible. It appears your current approach may not align with these guidelines. I suggest reviewing the following articles to gain a deeper understanding of how to design an effective model:
- Understand star schema and the importance for Power BI
- Model relationships in Power BI Desktop
- Bi-directional relationship guidance - Power BI
Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.
- danextian
Hi SteD
For drillthroughs to work, the column used in the target page must be the same column from the originating visual. For example, CalendarTable[date] --> FactTable[Date], if CalendarTable[date] is used in the originating visual, the same column must be used in the target page. It isn't enough that there is an existing relationship between the two.
v-pnaroju-msftCommunity Support
Thankyou, GeraldGEmerick, nielsvdc and danextian for your responses.
Hi SteD,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
We would like to inquire whether have you got the chance to check the solutions provided by GeraldGEmerick, nielsvdc and danextian to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.- v-pnaroju-msft
Hi SteD,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.
