Forum Discussion
query only top n from sql server by a column within specified date range
I have a large set of data that exceeds power bi row limits and am getting operation timeout. I want to query only top n rows from sql server based on one column and also want to have date filter on it.
"select top n * from table where date between @startdate and @enddate order by column_name"
I know I can do this by using query parameters but I want to have date filter on the report. So, let me make it clear
1) I have a date filter on report
2) I want to direct query only top n rows from sql server by a column within the specified date range set on filter.
The top n filter on visual queries all the rows from sql server and applies top n filter at last but I want to fetch only top n rows from sql server so that it would not give timeout and would be fast as well.
Thanks in advance!
7 Replies
Anonymous , Top on anything you can get by Rank or using top N filter at the visual level
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415amitchandak Thanks for replying. All those methods work only after fetching all the data.
I want to fetch only top n from sql server itself. I think I have made my need clear. Please be specific.
If you did not get my need right pls ask where exactly you want me to make it clear.
Thanks!
Hi amitchandak , does applyting top N filter query only top N from SQL server or query everything and apply after getting data ...?
Hi Anonymous ,
You could write code here:
Reference:
Hi v-xuding-msft , Thanks for the reply.I can write the query as
" select top n * from [table] where date between @startdate and @enddate ordey by [column_name]"
But for this I should be using @startdate and @enddate as query parameters and cannot have this as filter on report.
I want to have date filter on the report so I should not be using query parameters then the quey would be
"select top n * from [table] order by [column_name]"
If I write like this it would have alredy selected top n rows with out considering date range that is set by filter.
any ways and tricks to solve this..?
Thanks!
Hi Anonymous ,
You could reference the similar threads to have a try.
https://community.powerbi.com/t5/Desktop/Power-BI-parameter-for-SQL-Query/m-p/1083092#M501538
https://community.powerbi.com/t5/Desktop/How-to-use-parameters-in-Power-BI/td-p/651552
