Filtering tables in a direct query subset dataset

[Anonymous]

Long time lurker in these community boards, first time poster becuase I finally found a topic I cannot find the answer for.

I have next to no experience using query editor

I have a large data set (1.2GB) and reports run very slow. Most of our reports do not touch all of our tables. I created a subset dateset off our larger one and deleted unnecessary table for the majority of our reports. Now I would like to filter some of the existing table based on dates. I cannot figure this out becuase there are no existing queries and many of the options on the home ribbon are not available for selection.

I am trying to filter my "Assignment" table to only bring back rows where "Assignment Date Value" is on or after Jan 1, 2019.

This would elinimate over 3.6M rows from my almost 8.3M row table (almsot 45%)

[parry2k]

@Anonymous aha, so you are using Power BI dataset, unfortunaltey you can add more local sources but you cannot change the remote power bi dataset. To make changes to the power bi dataset, you have to open the original power bi report that has a dataset and make changes there.

I think now the dust is clear 🙂

[parry2k]

@Anonymous in continuation to @AlexisOlsonreply, what is your source? Are you connected to the Power BI dataset and using a live connection? What is your data source and how you are connected that is important to know and it looks like you have connected to Power BI dataset if you had connected to your backend source using Direct Query you would have sawed the queries in the Transform Data.

[Anonymous]

@parry2k thank you for your reply. I did clock on "Make changes to this model"

and then "Add a local model"

[AlexisOlson]

If you're connected via DirectQuery, there aren't any local tables to filter. Instead, every visual sends a filtered query to the data source. You can apply a report-level filter for the date but that shouldn't affect performance much unless your visuals were including data outside of that range (even then, I wouldn't expect there to be much of a difference).