cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Honey Member
Member

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: restrict loading of dataset

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-quer...

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Thejeswar Senior Member
Senior Member

Re: restrict loading of dataset

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

storage.PNG

 

 

Clicking on this, will display all the information related to the data sets

View solution in original post

7 REPLIES 7
Stachu Super Contributor
Super Contributor

Re: restrict loading of dataset

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-quer...

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Thejeswar Senior Member
Senior Member

Re: restrict loading of dataset

Hi @Honey,

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

Honey Member
Member

Re: restrict loading of dataset

Thanks alot for help

Honey Member
Member

Re: restrict loading of dataset

Hi, 

There are two options available.Not able to understand consequences of each.

Which one should i prefer.

 

Option 1: Writing sql while importing my view in power bi desktop with where clause.What impact will it have to performance since it stops query folding.

 

Option 2: to apply select specigif id from columns populating in edit queries?Will it not pull all data from sql server and then apply filter?

Thejeswar Senior Member
Senior Member

Re: restrict loading of dataset

Hi @Honey,

I would say Option 1 is the better approach of the two specified.

 

In Option 1, your data will be restricted even before it is imported to your report. In this means, you can restrict the volume of data getting imported in Import Mode. In case of Direct Query, it will certainly improve performance as compared to applying filter in Power BI, as every time your query runs the query is applied on the entire table to which it is connected to

 

In your Oprion 2, as you say, even if you filter for specific id in the Edit Queries Window, the Filters will be applied only after the entire data is pulled in to the report

Honey Member
Member

Re: restrict loading of dataset

I doubt based on blog shared above.If we need to limit data in dataset, we can opt for option 2.

I am confused that if i will publish this dashboard on service, will dataset hold all clients infotrmation or only those which are filtered out.

 

Continuation to this can we see dataset size which we publish on workspace, individually.

Thejeswar Senior Member
Senior Member

Re: restrict loading of dataset

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

storage.PNG

 

 

Clicking on this, will display all the information related to the data sets

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 97 members 1,320 guests
Please welcome our newest community members: