Drillthrough with measures

[Anonymous]

I'm struggling with Drillthrough due to an unusual way I've had to build a report, and I'm hoping that you folks can help. There's some explanatory background, which is a bit in-depth but it was a complicated problem to solve, sorry.

Let's say we're using WideWorldImporters and want to display how overdue particular invoices are. On the PowerBI report we could assign the Invoices to buckets depending on how overdue they are and then sum the Amounts, and display the data like so:

This is fine. I was then asked to build the report such that the end user could set an "Effective Date" using a slicer, and the report would update to reflect how overdue the invoices would be if the current date was actually the Effective Date. Two problems made this annoying:

1. Calculated columns only update on refresh, not when a Slicer changes value
2. Measures cannot be used as part of the axis of visuals like the bar chart above.

To solve this I created two new tables:

1. A table of dates spanning 90 days into the future
2. A table containing the bucket values I want to appear on the axis of the chart

I then created a slicer on the Date column of table #1 and a Measure to read the earliest date in the filtered table:

Effective Date = IF(MIN('Effective Date'[CalendarDate]) < TODAY(), TODAY(), MIN('Effective Date'[CalendarDate]))

And a measure on the Invoices table that calculates the Bucket that the Invoice would be in based on the Effective Date measure (it'd be amazing if this could just be the axis for the visual...but it can't 😞 )

Effective Date Bucket = 
    var CurrentDueDate = MAX('Sales Invoices'[DueDate])

    return (
        switch(
            true(),
            datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) <= 0, "0: Not Overdue",
            and(datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) > 0, datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) <= 30), "1: 0 - 30",
            and(datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) > 30, datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) <= 60), "2: 31 - 60",
            and(datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) > 60, datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) <= 90), "3: 61 - 90",
            and(datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) > 90, datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) <= 365), "4: 91 - 365",
            datediff(CurrentDueDate, 'Effective Date'[Effective Date], day) > 365, "5: 365+"
        )
    )

Finally, I set the Bucket column of table #2 to be the Axis value of the visual above, and created a new measure to calculate which invoices fall into each bucket based on the selected Effective Date:

Bucket Value = (
    var CurrentBucket = max('Debt Buckets'[Debt Bucket])

    return (
        CALCULATE(
            sum('Sales InvoiceLines'[LineAmount]),
            filter(
                ALLSELECTED('Sales Invoices'),
                'Sales Invoices'[Effective Date Bucket] = CurrentBucket
            )
        )
    )
)

This also works fine; I can use the left hand point on the date slicer to change the Effective Date measure, which causes the Effective Date Bucket against each measure to update, and then the Bucket Value measure refreshes and updates the visuals. All hunky dory.

That's the end of the background. The problem is this: this methodology breaks drill through completely. If I want to drillthrough to a Details page listing the invoices in the bucket I selected, I can't seem to achieve that on the visual that dynamically updates the Buckets based on the selected Effective Date. Instead, the drillthrough just returns all the invoices, which is useless.

How can I either:

a) force the drillthrough to only bring back invoices that are in the dynamically updated bucket?

b) re-do the "update bucket populations based on selected effective date" doohicky such that I don't experience this problem in the first place?

[Anonymous]

@Anonymous  - One possibility:

1. Create a disconnected parameter table that contains your bucket categories.

2. Create a Measure which checks SELECTEDVALUE of the parameter table in a SWITCH statement. So, the value that the measure returns will be dependent on which bucket you're checking.

3. Create your visual with the new parameter table and new measure.

Cheers!

Nathan

[Anonymous]

@Anonymousthat's basically exactly what I've done, but it severely disconnects the drillthrough unfortunately such that it no longer works correctly.

[Anonymous]

@Anonymous -

Ah, sorry about that - I started thinking about how it should work (without the drillthrough piece).

I guess the only way it would work to drillthrough on a disconnected table is to have all of the other measures on the drillthrough page also be affected by that table. Rows that aren't associated with the selected bucket would have BLANK values returned from the measures and therefore be excluded.

Hope this helps,

Nathan