Is there any way to fix the drill through ?

[PowerRobots99]

 Hello Friends,

Please refer link with DemoChallange.pbix file. 

https://drive.google.com/file/d/1AiwTOfo0gtSxiIF9F5Cgroh71MmAgM6U/view?usp=sharing

I have displayed numbers in matrix visual as shown.

Now, lets understand logic for the calculation - (Lets take exmaple for Location- A and Report date - 8/11/2024)

We need to find out how many transactions were scheduled between (5 Aug-11 Aug) from previous report week's data to report numbers for - 8/11/2024,

To do this, we need to select report date - 8/4/2024, and from "TransScheduleCompletionDate" we need to find out how many transactions are scheduled from (5 Aug-11 Aug), so, if for example 57 transactions were scheduled, we need to report this number under 8/11/2024.

I have this measure which work perfect, 

ScheduledTrans =

 var selDate = LASTDATE(Data[ReportDate]) --Capture current Report Date

 var lastweek = DATEADD(selDate,-7,DAY)

 var filDate =

 CALCULATETABLE( --modify filters

    FILTER(Data, [TransScheduleCompletionDate] > lastweek &&[TransScheduleCompletionDate] <= selDate)

    --only return rows after last week (excl 04) and to this week (inc 11/08)

    ,Data[ReportDate] = lastweek--replace current report filter with last week

    )

 RETURN

 COUNTROWS(filDate)

However, drill through on on "ScheduledTrans" numbers doesnt fetch any data as there is conflict between filter contexts.

Is there any way to fix this?  

[PowerRobots99]

@SamWiseOwl Sir, may i know if there any way to tackle this..

[rajendraongole1]

Hi @PowerRobots99 -you can modify the measure so that it works with both the visual filter context and the drill-through feature. 

ScheduledTrans =
VAR selDate = LASTDATE(Data[ReportDate]) -- Current report date
VAR lastweek = DATEADD(selDate, -7, DAY) -- Previous report date
VAR filDate =
CALCULATETABLE(
FILTER(
Data,
Data[TransScheduleCompletionDate] > lastweek
&& Data[TransScheduleCompletionDate] <= selDate
),
REMOVEFILTERS(Data[ReportDate]) -- Remove filters on ReportDate to avoid conflict
)
RETURN
COUNTROWS(filDate)

Hope the above measure helps. still issue exist please share pbix file.

[PowerRobots99]

Appreciate your response on this,

But i have already shared a link with .pbix file,

https://drive.google.com/file/d/1AiwTOfo0gtSxiIF9F5Cgroh71MmAgM6U/view?usp=sharing

Please refer the same for more details, 

thank you.

[rajendraongole1]

Hi @PowerRobots99 - Thanks for sharing will check this.

[PowerRobots99]

Thank you for your response,

ScheduledTrans =
VAR selDate = LASTDATE(Data[ReportDate]) -- Current report date
VAR lastweek = DATEADD(selDate, -7, DAY) -- Previous report date
VAR filDate =
CALCULATETABLE(
FILTER(
Data,
Data[TransScheduleCompletionDate] > lastweek
&& Data[TransScheduleCompletionDate] <= selDate
),
REMOVEFILTERS(Data[ReportDate])     -- Remove filters on ReportDate to avoid conflict
)
RETURN
COUNTROWS(filDate)

If we remove filter like this, it won’t give us correct count as per requirements,

As we need to find out how many transactions were scheduled between (5 Aug-11 Aug) from previous report date's i.e (8/04/2024) data to report numbers under - 8/11/2024, we have to keep that filter.

ScheduledTrans =
VAR selDate =
    LASTDATE ( Data[ReportDate] ) --Capture current Report Date
VAR lastweek =
    DATEADD ( selDate, -7, DAY )
VAR filDate =
    CALCULATETABLE (
        --modify filters
        FILTER (
            Data,
            [TransScheduleCompletionDate] > lastweek
                && [TransScheduleCompletionDate] <= selDate
        ) --only return rows after last week (excl 04) and to this week (inc 11/08)
        ,
        Data[ReportDate] = lastweek     --replace current report filter with last week
    )
RETURN
    COUNTROWS ( filDate )

[rajendraongole1]

Thanks for the info. 

I thought to preserve the ReportDate filter by modifying the calculation to filter based on the previous report date while maintaining the correct context for the current week. The key idea is to control the filter flow properly during drill-through without conflicting with the slicer.

Can you check below measure:

ScheduledTrans =
VAR selDate = LASTDATE(Data[ReportDate]) -- Capture current Report Date
VAR lastweek = DATEADD(selDate, -7, DAY) -- Previous Report Date
VAR transactionsScheduled =
CALCULATETABLE (
FILTER (
Data,
Data[TransScheduleCompletionDate] > lastweek
&& Data[TransScheduleCompletionDate] <= selDate
),
Data[ReportDate] = lastweek -- Ensure we filter on the previous report date
)
RETURN
COUNTROWS(transactionsScheduled)

Hope it helps.