Power BI - Select top N in "Direct Query" mode

[cuongle]

In "Import Data" mode, it would be easy for us to use DAX `RANKX` in order to support TOP N:

SupplierRank = IF(HASONEVALUE('Supplier'[name]), 
			RANKX(ALLSELECTED('Supplier'), [SumSpend],, DESC), 
			BLANK()
		)

And then we can choose SupplierRank <= 10 on Visual level filter, but on "Direct Query" mode, Power BI desktop prevents to use `RANKX` due to performance. Is there any workaround on "Direct Query" mode.

[ankitpatira]

@cuongle Are you using SQL data source then it would be easier to input sql query at the connection time to limit number of rows returned by donig select top(*), if you're using any other data source then also it would be easy to create index column in data source itself rather than trying in pbi desktop as it has restricted feature in DirectQuery mode.

[Anonymous]

@ankitpatira

It wouldn't be possible to construct an index column on the data source in my case. We have multiple filters on the dashboard and the top N should be on the basis of selections made by the user. The data source is huge ( more than 100 million rows for just one view that holds the fields on which it need to be filtered and ranked).

What approach would you suggest?

If there is any DAX that you can share, please share it so I can try if it works on the data set.

@cuongle it would be nice if you can share the approach you took, if you went ahead with the implementation.