How to create a custom “Past X Hours” slicer in Power BI with default selection?

[Zhinee]

I’m trying to create a custom time slicer in Power BI for a table that has a session time column (START_TIME). My goals are:

• Have a slicer with options like 2, 4, 6, 8, 12, 24 hours.

• Default selection should be Past 24 hours when the user enters the page.

• When the user selects a different option, the table updates dynamically to show only sessions in that range.

• I already have a visual that shows the session times, and I want to avoid using the built-in relative time filter because I’m planning to add a separate date slicer later. Users only want to use slicers on the page, not the filter pane.

Could someone provide some guidelines for implementing this kind of dynamic “past X hours” slicer in a report?

Thanks in advance!

[v-karpurapud · Accepted solution]

Hi @Zhinee 

Thank you for contacting the Microsoft Fabric Community Forum.

I am not entirely sure how your dataset is structured, but I reproduced the scenario on my end using a sample dataset, and the “Past X Hours” slicer functionality worked successfully. To help you better understand the implementation, I’ve prepared and attached a Power BI (.pbix) file ana snapshot that demonstrates the complete setup, including the disconnected slicer table, DAX measure, and dynamic filtering behavior. Please review the file at your convenience and let me know your observations or if you notice any differences compared to your dataset setup.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[Zhinee]

Thank you very much for the sample — it worked great on my session visual!

However, I’m running into an issue.
In my report, I also have another visual called Joblets. One session can have many joblets (a one-to-many relationship). When I click on a session, the Joblet visual correctly shows the joblets that belong to that session.
Also, when no session is selected, it should not show any joblets.

I currently use this measure to determine whether to show joblets:

IsSessionSelected =
IF(
ISFILTERED(SESSION_TRANS[Session ID]),
1,
0
)

However, it seems that the Past X Hours slicer is not affecting the Joblets.
For example, when the default is Past 24 Hours, I expect the Joblet visual to only show joblets that belong to those sessions from the past 24 hours.

Instead, the Joblet visual shows this error:

"Error: Resultset of a query to external data source exceeded the maximum allowed size of 100,000 rows."

This makes me believe that the Past X Hours slicer isn’t being applied to the Joblet visual.

Do you know how to fix this issue?

[v-karpurapud]

Hi @Zhinee 

The issue occurs because the custom “Past X Hours” slicer is disconnected from the data model and does not automatically propagate its filter to the Joblets table. This means that while the slicer successfully filters the Sessions visual through the DAX measure, the same filter does not reach the Joblets table. As a result, the Joblets visual continues to see all rows instead of being limited to the sessions within the selected time range.
 

When no session is selected, the Joblets visual attempts to display every joblet record. Since the “Past X Hours” slicer isn’t restricting the dataset, Power BI sends a very large query to the backend. The external data source, however, enforces a maximum result set limit of 100,000 rows, which leads to the following error:“Resultset of a query to external data source exceeded the maximum allowed size of 100,000 rows.”

Add the following measure inside the Joblets table. This measure applies the same “Past X Hours” logic used for Sessions and ensures the Joblets visual only displays joblets associated with sessions within the selected time range:

ShowJoblet =

VAR SelectedHours =

    SELECTEDVALUE(HourRanges[Hours], 24)

VAR CurrentTime = NOW()

VAR ThresholdTime = CurrentTime - (SelectedHours / 24)



VAR ValidSessions =

    FILTER(

        ALL(SESSION_TRANS),

        SESSION_TRANS[START_TIME] >= ThresholdTime

    )



VAR HasValidSession =

    COUNTROWS(

        FILTER(

            ValidSessions,

            SESSION_TRANS[Session ID] = MAX(JOBLETS[Session ID])

        )

    )

RETURN

IF(HasValidSession > 0, 1, 0)

Apply this measure as a visual filter on your Joblets visual (set to show items when ShowJoblet = 1). This will align the Joblets visual with the “Past X Hours” slicer and prevent the large query issue.

Regards,

Microsoft Fabric Community Support Team.

[GeraldGEmerick]

@Zhinee You could create a calculated column such as the following:

Hours In Past =
VAR _Current = NOW() // or use MAX( 'Table'[START_TIME] )
VAR _StartTime = [START_TIME]
VAR _Return = ABS( DATEDIFF( _Current, _StartTime, HOUR ) )
RETURN _Return