Drill-Through Page Not Filtering Correctly on DAX Measures

[PBIfolks]

Dear Community

I'm having trouble getting the correct related data to display in my drill-through. When I hover over the drill-through button on my bar chart, it's showing the entire table instead of filtering to the specific data.

I've added the drill-through field for all the relevant DAX measures, but it's not producing the expected results.

Can anyone suggest how to fix this issue? Any help would be greatly appreciated! PBIX File attached

PBIX File 

Many Thanks 

[Anonymous]

Hi @PBIfolks ,

Based on my testing, please try the following methods:

1.Create the new column to filter week number.

filter week = 
VAR LastCompletedWeekEnd = TODAY() - WEEKDAY(TODAY(), 2)
RETURN
    SWITCH(
        TRUE(),
        '20092024'[Dispatch Date] >= LastCompletedWeekEnd - 6 && '20092024'[Dispatch Date] <= LastCompletedWeekEnd, "01-week",
        '20092024'[Dispatch Date] >= LastCompletedWeekEnd - 13 && '20092024'[Dispatch Date] <= LastCompletedWeekEnd - 7, "02-week",
        '20092024'[Dispatch Date] >= LastCompletedWeekEnd - 20 && '20092024'[Dispatch Date] <= LastCompletedWeekEnd - 14, "03-week",
        '20092024'[Dispatch Date] >= LastCompletedWeekEnd - 27 && '20092024'[Dispatch Date] <= LastCompletedWeekEnd - 21, "04-week"
    )

2.Drag the filter week to the column chart Y-axis.

3.Create the new drill through page and drag the filter week to the Drill through.

4.Right click on the chart column, it is showing one ID.

You can also view the following document to learn more information.

Set up drillthrough in Power BI reports - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

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

[PBIfolks]

@Anonymous

Thanks for the reply and for sharing the sample PBIX file—much appreciated! I tried applying the same logic to a different use case, but unfortunately, it's not working as expected. I've attached the file for your reference.

Here’s a breakdown of the categories I’m working with:

• Category "AAA": Represents rows where [Correct Date] is non-blank and [Incorrect Date] is blank.
• Category "BBB": Represents rows where [Incorrect Date] is blank and [Issues Date] is non-blank.
• Category "CCC": Represents rows where [Issues Date] is blank and [Case Date] is non-blank.
• Category "DDD": Represents rows where [Case Date] is blank and [Approval Date] is non-blank.

In the attached PBIX file i was looking for ideally the first chart outcome which is currently got 4 DAX measures and with drill through, I tired applying SWITCH Function, but the result is not correct insetad of 4 BAR charts it is giving me only bar AAA value. Something is missing in my DAX measure. The correct outcome should be of 4 BAR value i.e. 6,2,1,1. 

BarChartMeasure_1 = 

SWITCH(
    TRUE(),
    CALCULATE(COUNTROWS('24092024'), NOT(ISBLANK('24092024'[Correct Date])) && ISBLANK('24092024'[Incorrect Date])) > 0, "AAA",
    CALCULATE(COUNTROWS('24092024'), ISBLANK('24092024'[Incorrect Date]) && NOT(ISBLANK('24092024'[Issues Date]))) > 0, "BBB",
    CALCULATE(COUNTROWS('24092024'), ISBLANK('24092024'[Issues Date]) && NOT(ISBLANK('24092024'[Reported Date]))) > 0, "CCC",
    CALCULATE(COUNTROWS('24092024'), ISBLANK('24092024'[Reported Date]) && NOT(ISBLANK('24092024'[Approval Date]))) > 0, "DDD"
)

I’d really appreciate your help in identifying where the issue might be.

PBIX File attached https://github.com/msncps/Power-BI-Community/blob/main/9-24drill%20through%20update.pbix 

[Anonymous]

Hi @PBIfolks ,

It may not achieve the desired result. X-axis must have field. Drillthrough only work with column filters. Try the following method as workaround.

Please copy the table.

Then, create the new column for AAA, BBB, CCC, DDD.

AAA Column = CALCULATE(COUNTROWS('24092024'), NOT(ISBLANK('24092024'[Correct Date])), ISBLANK('24092024'[Incorrect Date]))

BBB Column = CALCULATE(COUNTROWS('24092024'), ISBLANK('24092024'[Incorrect Date]), NOT(ISBLANK('24092024'[Issues Date])))

Drag the AAA, BBB column to the different column chart.

Select the column and Drill through.

Best Regards,

Wisdom Wu

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

[PBIfolks]

@Anonymous , thanks for your quick reply, ideally I like to have those count in bar to show like AAA, BBB..... is their a workaround?

[PBIfolks]

@lbendlin, Thanks - Is there a work around by creating a DAX in filer which link with the measure that cross filter only the drill through the related IDs.

[lbendlin]

Remember that you cannot create (meaningful) columns from measures.

Please describe your report's intent in more details.

[lbendlin]

That's correct. You cannot measure a measure.  Drillthroughs only work with column filters.

[PBIfolks]

@lbendlin 

Thank you for your response.

To clarify with detail outcome i expect - refering PBIX file, when I right-click on the bar, it should ideally display one ID, as the bar represents a single value. However, it’s showing the entire dataset with 7 rows, which is not the correct behavior.

I recall seeing a solution where someone used a single measure along with conditional formatting to achieve the desired result, but I’m unable to find that solution now.