Forum Discussion

cuongle's avatar
cuongle
Icon for Advocate II rankAdvocate II
10 years ago

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

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.

Need Help
Tips and Tricks

2 Replies

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

       

Related content