pbi desktop query limit rows changes？

[jinghua]

when i use pbi desktop direct query to connect my datasource with my connector,it works perfect，when i create a chart with different fields,  i found the query limit is not a fixed value，if i use only one demession filed in chart, the sql limit is 501 rows, if i use only one messure filed, the sql has not limit rows, if i use both demession and messure, the sql limit 1000001 rows,  pbi desktop is the latest version and i do not set any config about limit. Is this normal??? if yes , how it works? any documents about the sql limit?  i find many documents but does not work.... Please help~~~ 

[Anonymous]

Hi jinghua,

This is normal in Power BI. It supports upto 1 GB of data per visual. you can't see visual to be loaded if you have more than 1 GB of data.

You can filter data by removing unwanted Calculated columns, attributes, measures and apply more filter on the visual.

The best approach to have SSAS for your data model and calculations to be created. Power BI in-memory will supports upto 1GB whereas, SSAS can supports for huge volume of data. only the results will be shown in Power BI.

Regards,

Pradeep

[jinghua]

so why some query limit 501, some query limit 1000001, i do not set any limit config at pbi

[Anonymous]

This all get filtered out when you have mapping with other objects.

[jinghua]

thank you for you help, could you explain more detail? i am still confused~~~ can i set the query limit in pbi ? I want to know the where to set default query limt ~ as you know ,the database  will query with sql limit by datasource

[Anonymous]

Dear,

We can't set query limit in PBI. Its all about the filters that you have applied in SQL query, PBI filters and relationship between objects. 

Consider table1 has 10 records and table2 has 5 records and when you do a mapping between these 2 tables, then your result set should display only 5 records. Because, PBI does works only for Inner Join of both the objects.

You can do other joins in an another way. But, relationship would do an inner join in PBI.

Regards,

Pradeep

[jinghua]

thanks , but i just use one table at the chart ,not 2 tables join~  use one table in chart ,the query limit is not fixed? for example , table A has 10000 records, i chose a demission a1 filed which in A to the pbi chart ,i got the sql query in my console is "select a1 xxxxx from A xxx limit 501", but I use m1 witch is the demission in table A , I use m1 and a1 in pbi char ,the query in console is like 'select xxxxxxx from A limit 10000001' ? is this also normal??? how it works for only one table?  

[Anonymous]

Dear,

Did you remove limit from query console and check it?

I am not sure how PBI takes this? Because, PBI engine will store data in a columnar database. It seems, you may have restriction if you set limit in your query.

Other than this, it all depends on the filter you have applied.

Regards,

Pradeep

[jinghua]

no~ i do not remove any limit in my console~ yes, it is very strange how pbi set the query like 501， 1000001 and other limits~~~ ???

[Anonymous]

Hi @jinghua ,

Have you found a way for setting the limit?