Forum Discussion
Helper IMany to Many relationship filter - Excel Drill down
Hello,
Though this relates to excel not sure similar issues are there is Power BI.
I have tables which need many to many relationships for BUs. So I connected them using Table2 which has unique BUs. All three table are connected by BU. I have created below measure in Table1 to get the SUM of amount using filter from Table3. When used in the pivot table with filter from Table3-Combination it gives the desired result. But if I drill down the pivot I get all the data ignoring the filter from Table3. My primary purpose is to get the correct drill down for filters from Table3.
Please refer to this file.
https://drive.google.com/open?id=1Y1RJCraYYwdOoa-SmqFvVVXeOcxmz3rH
Is there a way to achieve this? Thanks.
3 Replies
v-yulgu-msftMicrosoft Employee
Hi hnsbhat,
When used in the pivot table with filter from Table3-Combination it gives the desired result. But if I drill down the pivot I get all the data ignoring the filter from Table3. My primary purpose is to get the correct drill down for filters from Table3.
This problem doesn't exist in Power BI. When you drill down to next level, the filter you applied will be kept.
Remember to set the "Cross Filter direction" to "Both". Otherwise, when you select an item from Table3, the amount values calculated based on Table1 won't be dynamically changed.
In this test, I used a Matrix. Add Table1[ID] and Table1[BU] to "Rows" of Matrix, add the measure to values.
Right click "1001" and choose "Drill Down"
Best regards,
Yuliana Gu
hnsbhatThank you Yuliana for looking into this. Yes in power BI seems to be working, but it seems there is no way to achieve this in excel.
- v-yulgu-msft