In power BI, how to achieve drill through based on partial values

[Anonymous]

Hi All

     I am attaching an excel workbook, which has two sheets.

1. A summary sheet called "BT_Allocations", which has summary data

2. A detail sheet called "HNS Detail" which has detail data

     I have marked an example in red font, which has a Services called "Aligne" in column C. The cost of which is 785,821.

I want the users to be able to drill through on another sheet, which shows detailed data. This is straight forward drill through if there was only one app called "Aligne" in the detailed sheet. But if you go to "HNS Detail", the first column called App, has only one value called "Aligne" if its of Type Direct. But multiple values if the Type is shared. All the apps in shared type are seperated by ".;". Also the number of shared values shared is indicated by a number is brackets in the column "Type".

If we sum up all the values (Direct + Shared) in the column "YTD-Per App", it matches to 785,821 which is the same value in summary. Now the business want to be able to drill through from the summary sheet into the detailed sheet, still showing not just direct "Aligne" but also the values where "Aligne" occurs in the app in which it is shared. Also they want to the know other values that the app is shared with (i.e the remaining values of the app). So I think this will involve a partial join between Services column in "BT_Allocations" and App field in "HNS Detail" and display all the columns of HNS Detail which has even a partial match.

Pls can someone help me,how this can be achieved?

Thanks and Regards

Amit

[v-eachen-msft]

Hi @Anonymous ,

You could try CROSSJOIN() function and use SEARCH() function to filter the crossed table.

You can provide a table-size data or upload your files to OneDrive and share the link here.

[Anonymous]

Hi

    Many Thanks for your prompt response.

Would be great if you can show me the solution with code and example.

Here is the link for the one drive file as you had suggested

https://1drv.ms/x/s!AtnpFb8rOR4osD2KeXbg_G_RquCW?e=LPle5E

Thanks in advance

Rgds

Amit

[Anonymous]

Hi

Since I cannot excel sheets on this request, I am attaching pictures of the two sheets for everyones information.

Rgds

Amit

BT_AllocationsHNS Detail