Many to Many relationship filter - Excel Drill down

[hnsbhat]

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.

[v-yulgu-msft]

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

[hnsbhat]

Thank 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]

Hi @hnsbhat,

If you want this feature available or want any workaround in Excel, I would suggest you post question on a more related forum to get professional advice.

Regards,

Yuliana Gu