Power BI Dataset to Excel Connection Filter Limitation

Power BI
1

Hello Members. I have a general question about Power BI Dataset usage in Excel. The standard functionality is to connect to the Power BI dataset in Excel and all of the data from the Power BI Dataset is loaded into Excel and a Pivot Table is created that allows you to drag the fields you need and apply any filters after all of the data has been loaded into Excel first. Currently there does not appear to be a way to filter or limit the data that gets loaded into Excel until after all of the data is loaded first. It would be helpful if there was a way to limit the data first and then load the required data into Excel after that. Is anyone aware of a way to do this?

I have some use cases where I would like members of my finance team to use some of our published and promoted Power BI Datasets but it contains millions of rows of data and when its initially connected in Excel the performance is very slow because there is no way to filter or limit the data. Has anyone else encountered this type of use case and found a way to resolve it? Any help or guidance you can provide is appreciated.

1 Like
2

Bumping this post for more visibility.

1 Like
3

Hi @mford - Try to implement Row Level Security on the Dataset and assign User to the Roles. When they will connect through Excel then only data for which access is available shall be visible instead of entire dataset.

Thanks
Ankit J

2 Likes
4

@mford When you connect Excel to Power BI Service, Excel is only connected to the dataset with a connection string and it doesn’t physically store/load the data. If there is performance issues, then you need to optimize the measures or maybe check if there is a VPN/Latency issue.

1 Like
5

Hi Ankit. Thanks for your reply. I appreciate your input.

1 Like
6

Thanks for the reply Antriksh. I have some ideas on what to try. I’ll post back once I’ve tried them out.

Thanks,
Mike

2 Likes
7

Hello @mford following up if you were able to try a solution that works well with your inquiry?

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi @mford, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!