Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
Super User

Working with Timestamps in a DIRECT QUERY Model

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.

rsbin_0-1666982633247.png

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.

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

@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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.