Add a date range filter by selecting an event (that is linked in the background to a start/end time)

[ArmandC]

I have a set of data (temperature, stirring rate etc) that I would like to filter by date range (ie between start and end dates), however I don't want to have to look up the dates, because these are the start and end dates of specific events. I would like to choose the event, and have the temperature and stirring rate plotted automatically. 

See the data examples (mocked up quickly on excel), with the output I would like to have:

Batch	Step	start time	end time
1	charging	01/01/2022 00:00	01/01/2022 04:00
1	mixing	01/01/2022 04:00	01/01/2022 11:00
1	cooling	01/01/2022 11:00	01/01/2022 12:00

Tag	Time	Value
temperature	01/01/2022 00:00	25
temperature	01/01/2022 00:30	24.7
temperature	01/01/2022 01:00	23.8
temperature	01/01/2022 01:30	20
temperature	01/01/2022 02:00	24
temperature	01/01/2022 02:30	24.5
temperature	01/01/2022 03:00	22.9
temperature	01/01/2022 03:30	24
temperature	01/01/2022 04:00	31
temperature	01/01/2022 04:30	31
temperature	01/01/2022 05:00	32
temperature	01/01/2022 05:30	34
temperature	01/01/2022 06:00	31
temperature	01/01/2022 06:30	32
temperature	01/01/2022 07:00	33
temperature	01/01/2022 07:30	33
temperature	01/01/2022 08:00	33
temperature	01/01/2022 08:30	32
temperature	01/01/2022 09:00	33
temperature	01/01/2022 09:30	32
temperature	01/01/2022 10:00	32
temperature	01/01/2022 10:30	33
temperature	01/01/2022 11:00	5
temperature	01/01/2022 11:30	3
temperature	01/01/2022 12:00	2
stirring rate	01/01/2022 00:00	0
stirring rate	01/01/2022 00:30	0
stirring rate	01/01/2022 01:00	0
stirring rate	01/01/2022 01:30	0
stirring rate	01/01/2022 02:00	0
stirring rate	01/01/2022 02:30	0
stirring rate	01/01/2022 03:00	0
stirring rate	01/01/2022 03:30	0
stirring rate	01/01/2022 04:00	100
stirring rate	01/01/2022 04:30	100
stirring rate	01/01/2022 05:00	100
stirring rate	01/01/2022 05:30	100
stirring rate	01/01/2022 06:00	100
stirring rate	01/01/2022 06:30	100
stirring rate	01/01/2022 07:00	100
stirring rate	01/01/2022 07:30	100
stirring rate	01/01/2022 08:00	100
stirring rate	01/01/2022 08:30	100
stirring rate	01/01/2022 09:00	100
stirring rate	01/01/2022 09:30	100
stirring rate	01/01/2022 10:00	100
stirring rate	01/01/2022 10:30	100
stirring rate	01/01/2022 11:00	30
stirring rate	01/01/2022 11:30	30
stirring rate	01/01/2022 12:00	30

I can plot everything and get this:

But ideally, I could use a dropdown menu or something similar to filter by step:

So far I can plot my trends as a whole, and I can use sliders to choose date ranges, but I would love to be able to select a step, and power bi automatically selects the date ranges for me. My end goal is to be able to compare steps on multiple batches, but I will get there later 🙂

[v-kkf-msft · Accepted solution]

Hi @ArmandC ,

Please try the following formula and show items when the Measure is not blank.

Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Time] >= MIN ( 'Date range'[start time] )
            && 'Table'[Time] < MAX ( 'Date range'[end time] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@ArmandC Start with this: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

and this: https://excelwithallison.blogspot.com/2021/08/categorical-date-slicer-in-power-bi.html 

Then try this: https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html