Forum Discussion
restrict loading of dataset
Can i restrict number of rows to load in dataset by applying where clause.
Requirement is i dont want complete data to be imported in pbix but need data for 10 clients only.
Instead of applying where clause in sql end, can i do something at pbix end so it doesnt load all clients data?
Kindly help.
if you filter the customers within the PowerBI interface, it should apply query folding to apply the filter
https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-querypower-biexcel-get-transform/In Option 2, your entire data is brought in, Only after that your filters are applied. In Other Words, Your report will have all clients information, but your visuals will get only the restricted clients information as you are applying the filters to your dataset before using in Visuals
Yes you can see the datset size in your Group Storage Section. Find below the image
Clicking on this, will display all the information related to the data sets
8 Replies
- Stachu
Community Champion
if you filter the customers within the PowerBI interface, it should apply query folding to apply the filter
https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-querypower-biexcel-get-transform/- Thejeswar
Super User
Hi Anonymous,
Yes you can filter the data set in PBIX Power Query window which you get when you click on edit Queries, just like the way you do in excel.
Click on the column drop down and filter the items that you want
In Option 2, the entire dataset is first loaded into Power BI, and then filters are applied at the visual level. This means that while the report contains all client data, only the restricted client information appears in the visuals.
However, loading the full dataset before filtering can impact performance, especially with large datasets. A more optimized approach is to apply filtering at the source level using a SQL query before loading data into Power BI. This reduces the dataset size and improves report performance.
For example, instead of bringing in all client data and filtering later, you can use a SQL query with a WHERE condition to load only the required data:
SELECT *
FROM Clients
WHERE Region = 'West' -- Load only data for the 'West' regionThis ensures that only relevant data is imported, leading to faster refresh times and improved report efficiency.
Regarding dataset size, you can check it in the Group Storage Section in Power BI Service, where it provides details on how much space your dataset occupies. Below is an image showing where you can find this information.
