- connect to any SQL database from Power BI in direct query mode
- create table visual with visual level filtration TOP(N)
- catch queries, that Power BI generates automatically
You will see the visual TOPN filter does not force Power BI to generate SQL queries like that: select top(n) * from tbl order by col.
Instead of that it generates query like that select * from tbl, then pull entire result set on its own side, then order by it LOL, then get top(N) rows. So in the case of SQL Server, all mentioned work is done on Power BI side. It is bad approach if i working with big tables. I hope uys you can fix that easily.
But in the case of SSAS it works as expected, it pushes down topn dax expression to SSAS and does all dirty work at SSAS side.
I use both approaches, in the case of SQL Server i have ROLAP cube, in the case of SSAS i use MOLAP cube...
1. DAX is supported by SSAS natively. Sending the DAX formula to the SSAS Server would be a great idea as you can see from the backend. SQL Server doesn't support DAX. If we translate a DAX formula, which we can't expect what it would be, to a SQL statement, I don't think this would be efficient.
2. The SSAS model is defined all in the Server side. We can't do any customization in the Power BI as we can see from the greyed interface. So it would be a good idea to send the work to the server that already does the work. But it's different for SQL Server. If we use a measure in the "By value" field, and if Power BI translate it to SQL statements then send it to the Server, Power BI has to evaluate the measures (DAX) again when it receive the return values because of the other measures. This is obvious inefficient.
Community Support Team _ Dale If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you please forget about SSAS? Power BI + SSAS work well together. My question is not about SSAS or Power BI + SSAS. I mentioned it as an example where things are working well. Dont speak about that please.
Lets speak about SQL Server + Power BI and about how Power BI's feature TopN filtration is implemented in this case
It does not generate queries like that (as expected😞
select top(100) * from tbl order by Site_Url
It generetes like that
select * from tbl
And then pulls ALL resultset, then it does order by on Power BI side and get 100 rows.
Please dont speak me about it cannot be implemented. It must be implemented, because if you try to use Power BI on big tables in real life without the feature, Power BI is meaningless. All concurent products can do that in eficcient way.
I have found that power bi initially sends out query only calulating measure. After getting measure values it takes top N from it and again sends back a query with where clause " columnname in (value 1,value2, . . . . ,valune n) . so it would atlast fetch only top n rows.
Even I haven't found a way to make it work like you asked i.e to have "select top(n)".
If you have found the answer please do post it here.