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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.