Can you limit or filter the dataset data that loads into a Power BI Desktop Report?

[davebourgeois87]

Hello,

I am currently working with a large dataset that has direct query access to a SQL server database. I am currently trying to create a report that loads the data into the report by getting data via the Power BI datasets option. The problem that I have is that the entire dataset loads into the report, which makes the report extremely unwieldy. The total dataset has perhaps 200 columns and 100 million+ rows. 

I would like to break the dataset up into ~50 or so separate reports so that they are more manageable and since the organization that I work for is aligned to those breaks anyway. So, for example, if the tables below represent my desired outcome, I would start with:

Power BI dataset

Sector	Category	Brand	Period	Market	Fact
I	1	A	2021	United States	Dollars
I	2	B	2021	United States	Dollars
I	3	C	2021	United States	Dollars
II	4	A	2021	United States	Dollars
II	5	B	2021	United States	Dollars
II	6	D	2021	United States	Dollars
III	7	A	2021	United States	Dollars
III	8	E	2021	United States	Dollars
III	9	F	2021	United States	Dollars
IV	10	C	2021	United States	Dollars
IV	11	G	2021	United States	Dollars
IV	12	H	2021	United States	Dollars

And then somehow filter the dataset by category so that I would end up with:

Report 1

Sector	Category	Brand	Period	Market	Fact
I	1	A	2021	United States	Dollars

Report 2

Sector	Category	Brand	Period	Market	Fact
I	2	B	2021	United States	Dollars

And so on. The important part is that the filter is applied during the data load process, and not with filters within the report. 

Is this possible? Or is there a better way to do this? Thanks in advance. 

[KNP · Accepted solution]

If it is setup as direct query, why is the entire dataset loading into the report?

The first thing I would be looking at doing is to create aggregation tables.

GuyInACube have a good playlist of the why's and how's.

(https://www.youtube.com/watch?v=EhGF372t0sU&list=PLv2BtOtLblH0cQ7rWV2SVLGoplKdy0LtD)

[KNP · Accepted solution]

If it is setup as direct query, why is the entire dataset loading into the report?

The first thing I would be looking at doing is to create aggregation tables.

GuyInACube have a good playlist of the why's and how's.

(https://www.youtube.com/watch?v=EhGF372t0sU&list=PLv2BtOtLblH0cQ7rWV2SVLGoplKdy0LtD)

[davebourgeois87]

Hi @KNP ,

Thank you for the quick response. First, to address your question, the dataset was published by the team that manages the SQL server, and is essentially just a dump of the entire database. I do not have access to the SQL database itself, just the dataset that they published. I'm getting the data from the published data set: 

Not from the SQL server directly. When I use that option to load the data, I don't have the ability to transform the data.

Between your message and the video that you recommended, it looks like I need to use the Get Data from SQL Server option to hook up to the database directly and then either implement a direct query to limit the data, or create aggregation tables using Power Query in the Transform Data stage. Does that sound correct?

Sorry if this isn't making sense. I don't have a technical background. Just a business analyst trying to make the switch from Spotfire to PBI. Creating reports that link to an Excel report are no problem, but trying to link up to this SQL database has been a real challenge. I appreciate any help that you can provide. 

[KNP · Accepted solution]

That makes perfect sense and TBH that is a good design to have the dataset separate from the reports.

The aggregation tables should be created in the dataset that you are connecting to. The team that manages that dataset would need the knowledge/detail to know the aggregations required. Without seeing the data/data model it becomes a bit of a best practices/philosophical discussion.

If you have to jump through a lot of hoops to get changes made to that sort of thing, and you have the option to go directly to the SQL database, then by all means, do that. If you don't have that knowledge/access then talk nicely to your SQL person.

Depending on your data, one thing you could try now is adding filters to all pages to limit the data as you mentioned earlier. I doubt that will make a significant difference but is dependent on where the performance issues exist. Might be worth a try.

As far as where to create the aggregations, you have options. Power Query (Transform Data) may not be the best place, as you can run into some pretty severe limitations with transformations when using direct query sources. You can create the summaries using DAX. Ideally, you'd be better creating them in SQL with stored procedures/views or well crafted queries that return exactly what you need (i.e. no further transformations required). ALWAYS best to do these things as close to the source as possible.

I haven't really solved the problem for you but I hope this helps.

[davebourgeois87]

Hi @KNP ,

This still gives me the information that I need, and I am immensely grateful, thank you!