Unable to obtain the list of items, How to fix this issue?

[JoeThomas592]

I am conducting Direct Query SQL Server Database Connection, and receive this message when filtering "Unable to obtain the list of items." This table has 1 million customer ids. I understand it is large for Direct Query, however, would like to at least see the top 1000 rows on filter preview.  We are utilizing columnstore indexes

(1)

I want to utilize Basic Filter, and Not do a specific search. Is there anyway to change memory settings or row limit fitler settings in Power BI?

(2) Additionally, with SSAS when connecting to cube with 1million customers, it would show the first 1000 or so. How do I get it to be like SSAS?

[Bharath106]

Any update on this issue? I basically have 33 lakh of data in sql and my embedded report in dynamics is not showing filters for it. Its working fine in BI service

[Boz]

I have precisly the same issue except i just want the basic filter to work on countries

I only have about 4956 records to filter on not millions

Any solutions?

[Anonymous]

Hi @JoeThomas592 
Did you try to use the advanced SQL statment?

[JoeThomas592]

well I want All the data, only just the first few 100 rows on the filters, so it does not time out, I am aware of the data query options,

are you saying if I have an aggregation of more than 5 million rows, this will work?