How to Drillthru to Specific Records of a Table Based on Date Criteria

[Shelley]

Hi All,

There's probably an easy way to do this, but I'm missing the trick.

We have repair order data records for ALL customers based on order submit date, which is related to the date table.

We have specific customers (a subset of customers) we're targeting and tracking results for a specific period of time. I have the targeted customers loaded as a separate table in the model.

This time period is referred to as the "adoption" period so it has a start and an end date.
I am calculating a count of repairs as a column on the target customer table for the adoption period like this:

Current Adoption Repair Count =

CALCULATE(

[Count of Repair Units],

DATESBETWEEN(

    'Date Table'[Date],

    'Fishing'[Adoption Period Start Date],  

    'Fishing'[Adoption Period Tollgate]))

One of my teammates now wants me to add a drillthru to the report from the customer summary that shows things like the customer, adoption start date, tollgate date, the current adoption repair count, etc. to drill thru to all the repair order details. That is to show only the records from the repair order table for each specific customer where the 'Repair Order'[Submit Date] is >= 'Fishing'[Adoption Period Start Date] AND 'Repair Order'[Submit Date] <= 'Fishing'[Adoption Period Tollgate]. I have the keep all filters switch set to "on", and the page filters to the customer's orders; however, it shows all orders because the adoption period crosses multiple dates.
I could create a separate table for the adoption period dates for each customer, but I was wondering if there's a simpler way to do this with perhaps a virtual table or a measure. So, I'm looking for advice on the best way to filter to show just these repair orders for a customer within their unique adoption period.

[Anonymous · Accepted solution]

Hi @Shelley ,

Please have a try.

Filtered Repair Orders = 
CALCULATE(
    COUNTROWS('Repair Order'),
    FILTER(
        'Repair Order',
        'Repair Order'[Submit Date] >= MIN('Fishing'[Adoption Period Start Date]) &&
        'Repair Order'[Submit Date] <= MAX('Fishing'[Adoption Period Tollgate])
    )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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