Drillthrough from measure not taking internal measure filters

[Anonymous]

Hi community,

I got a source data by Id that has relationships with a calendar table:

Datasource:

relationships:

I created a visual table where each column is a measure (Countrows) with its own filters. For example:

Records recevied = CALCULATE(COUNTROWS(Data),Data[Received?]=1, USERELATIONSHIP('Calendar'[Date],Data[Received date]))

visual:

I would like to drill through a details page and show the specific rows information from the source data.

The drillthrough is taking only the 'Campaign ID' and returns the total number of leads... not the ones contained in the measure. 

Any workaround to drillthrough will be appreciated.

Thanks for any help!

[v-yingjl]

Hi @Anonymous ,

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li

[v-yingjl]

Hi @Anonymous ,

If you want to use drillthrough to show the data, these two visuals must have the same fields first.

For example, you can create a new page that includes a table about 'connected':

Put your connected measure in the drillthrough filter, filter the table to only show connected data.

Now you can write click your measure in the table of the main page to use drillthrough to show the corresponding data.

You can also refer this microsoft document abour drillthrough: Set up drill through in Power BI reports 

In addition, I prefer you to use tooltip to show the data directly, refer: Create tooltips based on report pages in Power BI Desktop 

My sample file attached that both include drillthrough and tooltip hopes to help you too, please try it: 

Drillthrough and tooltip.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl 

As I need to drill through every measure in the table (in this example 3 measures), This means I need to create 3 different drillthrough pages, one per each measure. I would need to set a drillthrough page using filter connected?=1, then a different drill through page using filter received?=1, etc

This is just an example I created with dummy data, but the real data contains 10 measures in the same table. That's why I was trying to find a solution to create only 1 drill through page.

[v-yingjl]

Hi @Anonymous ,

If you want to create only one drillthrough page, maybe you should put all related visuals in this page and all related measure in the drillthrough filter.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[sanimesa]

@Anonymous  There is only campaignID in the visual that will pass into drill through. If you want another key field, you have to place it into the visual. Can you please put morre details on what you are expecting to happen? 

[Anonymous]

Hi @sanimesa 

If I click in any number, let's say Records connected, Campaign B.

I would expect to see a table with 20 records in the drillthrough, but I see the total number of records in Campaign B (33 records).

is there any way that the drillthrough take the filter implicit in the measure (Connected?=1)

Thanks,

[sanimesa]

@Anonymous  Ok, I think you may be able to set it up as following:

1. In your table, create a calculated column which will create a unique value fo each of the connected, received etc attribute.

Something like:

Status = IF(connected=1, "Connected", IF(received=1, "Received", ""))

Then, you place this column on you matrix.

2. Modify your measure to simply do the countrows, since it will be placed in the context of the cell. it will pick up the correct value.

Now the drill down will work because it will be able to pick up the context of the artificial  column and apply that to your destination table. 

[Anonymous]

Hi @sanimesa 

unfortunately I can't use that logic as a lead ID can be recevied, connected and opportunity at the same time. These are flags as part of a funnel. For example, L-002 was recevied and connected, L-004 was only received, and L-008 was received, connected and became an opportunity.

Lead Id	Campaign ID	Received?	Connected?	Opportunity?
L-002	Campaign A	1	1	0
L-004	Campaign A	1	0	0
L-008	Campaign A	1	1	1

if this were the data, my visualization is counting: 3 leads recevied, 2 connected, 1 opportunity.

[sanimesa]

@Anonymous  It will nott automatically get the connected fom the measure. You maybe able to put the connected flag in the visual itself and select keep all filters. See if that works.

Otherwise a wokaround could be to simply put a filter on you target page or visual where connected = 1.

[Anonymous]

Spoiler

@sanimesa That would work for the measure Records connected only, but it's not working for the rest of measures (Records received and Opportinty) each column on the table is a different measure with its own flag as filter.
Records received = CALCULATE(
                                           COUNTROWS(Data),
                                           Data[Received?]=1,
                                           USERELATIONSHIP('Calendar'[Date],Data[Received date]) )

Records connected = CALCULATE(
                                             COUNTROWS(Data),

                                             Data[Connected?]=1,

                                             USERELATIONSHIP('Calendar'[Date],Data[Connected date]))

Opportunities = CALCULATE(

                                   COUNTROWS(Data),

                                   Data[Opportunity?]=1,

                                   USERELATIONSHIP('Calendar'[Date],Data[Opportunity date]))

I'm not sure if creating a drillthrough page for every measure would be a efficient solution, as in the real data I have more than 10 measures in the same table.