Working with Timestamps in a DIRECT QUERY Model

[rsbin]

Good Afternoon,

I've finally hit a wall myself and now reaching out for assistance.

As stated, I am working with a Direct Query model, so don't have all Calculate functions available to work with.

I have a table of records, and for simplicity, a series of timestamps:

timestamp

10/28/2022 13:39

10/28/2022 12:59

10/28/2022 12:59

10/28/2022 12:59

10/28/2022 12:57

10/28/2022 12:56

10/28/2022 11:57

10/28/2022 11:56

10/28/2022 10:52

10/28/2022 10:51

My Query shows records for the Past 24 Hours (approx 2000 records). 

But Users now want the ability to filter by number of hours shown.

So have developed a slicer like this where user can select anywhere between 1 and 24 hours.

Based on the Max Time of my dataset, I can calculate the MIN Timestamp to display:

Timestamp_MIN = [Timestamp_Last] - ( [SelectedHourSlicer]/24 )

I was hoping to create a Calculated Column Flag whereby if [timestamp] >= [Timestamp_MIN], then 1, else 0.

PBI is objecting to the fact that I am using MAX to calculate [timestamp_Last].

Hoping someone has the experience to show me a possible path forward to get a Filter working correctly.

[ppm1 · Accepted solution]

You should be able to do that with a dynamic M parameter and a filter in your query (that folds back to your source). Please see this information.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Pat

[rsbin]

@ppm1 ,

Thanks kindly for the suggestion.  Did some very preliminary research on the weekend and at this time, I believe this should be a viable solution.

Thanks again and Best Regards,