Only show subset/sample of data with Direct Query

[Mr_Glister]

Hi,

I have a table with many millions of Image URLs in it, and connect to it through Direct Query. 

We can assume the columns in the table are COUNTRY / STATE / WAREHOUSE / DATE / VALUE / IMAGE URL. 

Country, State, Warehouse, and Date will be slicers in my report. The important thing -> because of a data sensitivity issue I want to allow the report users to always only see max 10 (random) images, given the slicer selections they've made. 

I thought I could simply put a TOP N filter based on VALUE on my visual but that doesn't seem to work. 

The SAMPLE function also looked promising, but this also returns an error ("The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.")

Because I want to show 10 images no matter what selection a user makes in the slicers, I also can't limit the rows in the SQL statement already. 

Any ideas how I can achieve what I need?

[Mr_Glister · Accepted solution]

I realized instead of doing something complicated in PowerBI it makes much more sense to do something slightly more complicated directly in the SQL statement.

For anybody else with the same problem, here is what was the perfect solution for me:

select * from prod.sales
where 
     invoice_date > current_timestamp - interval '30 days'
qualify
     row_number() over(partition by country, state, warehouse, date order by value DESC) <=10

[amitchandak]

@Mr_Glister , Create a rownumber measure based on the columns used in visual and then use that as a visual level filter

example

New Row Number = ROWNUMBER(ALLSELECTED(Sales[Order No]),ORDERBY([Net],DESC))

or

New Row Number = ROWNUMBER(ALLSELECTED(Sales[Order No], sales[Item id]),ORDERBY([Net],DESC))

Power BI - New DAX Function: Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

[Mr_Glister]

Hi @amitchandak,

I tried your approach and also watched your video but I'm running into the same problem as when I use TOP N in the visual filter.