There are a few ways to filter your data within Power BI depending on how you want to view your data.
Power Query Editor Filtering
Filtering the query directly should be used to take out data that you will not need for your report. This is helpful for shrinking the size of data you are bringing into Power BI, which can improve report performance and gets rid of unnecessary information for users.
To filter the query access the Power Query Editor by selecting “Edit Queries” from the home section of the top toolbar.
Once within the editor filter any column by clicking the drop-down arrow next to the column name. Depending on the type of column there is text, number or date/time filters that you can choose from. You can also remove empties or select which values to keep from the list.
The ability to filter individual visualizations allows you to show only specific information, but still maintains all your data in the background.
To apply a filter to an individual visualization, select the visualization and add your filter options into the filters tab on the right-hand side. A visualization is selected when brackets appear around the edges. Drag the field to filter by onto the tab and choose from the types of filters, basic, advanced or top n filters. If using a date type field there is also relative date filtering available.
Here we show a basic filter where the visualization will only show records with a status of inactive.
In the below advanced filter the visualization will only show records where the tenure in months is less than 1.
The top n filtering option allows you to show the top or bottom number of records based on a particular field. In this example we are showing the top 10 customers by document amount.
Relative date filtering allows you to select a specific timeframe. The example below shows relative date filtering showing all records with an end date within the last calendar year.
Filtering at the page level has the same benefits as filtering at the visualization level, but it applies to the whole page. This saves time as you do not have to set up a filter on each visualization. You can apply a page level filter within the filters tab on the right-hand side. Filters can be applied to a single page or all pages in a report.
Slicers do not filter the data for the report but gives users the ability to manually select the filter they wish to apply.
The slicer visualization is the icon with a funnel in the right-hand corner. Once you have the visualization selected pull the field over you wish to filter by.
The slicer created above creates a filter for job number. Below is what the slicer looks like on the report. When a user selects a particular job number from the list all corresponding visualizations on the page will update.
Filtering can help keep your reports streamlined yet maintain flexibility in ability to access data. It is important to think about how you want to work with your data when selecting how you will filter your report and you may even use multiple filtering options.
-Jerica Coleman, CRM and Power BI Consultant