Drill through filter based on a measure

[Ajinkya369]

The goal is to setup a drill through filter on the table for the page Expenditure that will do the following:

There are two pages Expenditure and Thorough Table in the Pbix file.

Example: When we right click on January Months Amount (50) for Expenditure Description Food   -> Drill through -> Thorough Table

In the thorough table it should only display January month’s (selected month's) data not all the months Expenditure.

Note: The scenario shown here is just for example purpose. We cannot use the month column for the table, we must use measures and need to do the drill through filter to display the individual values that compose the result of the measure.

Pbix file : Drillthrough_Scenario.pbix - Google Drive

Expected Output:

Expected Output

Best Regards,

Ajinkya 

[v-jingzhang]

@Ajinkya369 

Has this problem been solved? If so, kindly Accept the appropriate reply as the solution or post your own solution to help close this topic. More people will benefit from it. Otherwise if you are still confused about it, please provide more details about your problem. Thanks a lot.

Best Regards,
Community Support Team _ Jing Zhang

[speedramps]

Please consider this solution and leave kudos

Click here for PBIX 

The root cause of your problem is a bad data calendar table.  You can get a good calendar table here ...
Calendar table 

You have created Jan, Feb and Mar measures because you had a bad calendar table.
As soon as you do this you lose your row and column 'context' to drillthrough.

You know that measures are are just for Jan, Feb and Mar but the computer does not.
The computer is dumb and returns the measures (exactly like you asked) rather than the context (which is what you really need).
That is why it shows all months when you drill through !

If you use the good calendar table then you can use the "Month Year"  name instead of Jan, Feb, Mar measures.
When you drillthrough the computer will now know the row and column context for the Expenditure and Month Year.
So the drillthrough will only show the date details for the cell you clicked on.

I hear you say "Note: We cannot use the month column for the table, we must use measures" but please reconsider this strategy and consider the difference between row, columns and measures context. If you use a month column then the context will be the month but if you use a Jan, Feb & Mar measures then the drillthrough context will always be all months.   Understand the problem ?  😁