Multiple queries with multiple parameters

i think the exact feature you are looking for to fix this is called dynamic m query parameters. this is literally built for your exact problem.

instead of power bi pulling the whole table and then filtering it locally, dynamic m parameters let you bind a report slicer directly to a power query parameter. that parameter gets injected directly into your native sql query (or tvf/stored procedure) so the database only processes and returns the exact sliced data.

here is how you set it up:

first, go into power query and create a new parameter.
next, modify your directquery sql statement to include that parameter in the where clause.
close and apply. then go to the model view in power bi desktop.
click the column you are using for your slicer. in the properties pane on the right, expand the advanced section, look for "bind to parameter", and select the m parameter you just created.

now, whenever a user clicks that slicer, power bi passes the value directly into the sql query before hitting the db. it makes a massive performance difference and keeps your real-time requirement intact.

here is the ms docs walkthrough to get the exact syntax right, especially if you need to allow users to select multiple values at once in the slicer: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

hope this solves your directquery nightmare! if this gets your dashboard running fast again, please mark this as the accepted solution so others can find it.