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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Mr_Glister
Advocate II
Advocate II

Only show subset/sample of data with Direct Query

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?

1 ACCEPTED SOLUTION
Mr_Glister
Advocate II
Advocate II

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

View solution in original post

3 REPLIES 3
Mr_Glister
Advocate II
Advocate II

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
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

 

Mr_Glister_1-1713510127582.png

 

 

Mr_Glister_0-1713510082500.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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