How to Select All in Power BI

Adding a "Select All" option to a Power BI slicer seems like it should be simple, but it's a common point of frustration for many report creators. While Power BI's default behavior is that an untouched slicer includes all data, this isn't always intuitive for end-users who expect a checkbox or button. This article will walk you through several ways to create a clear "Select All" function, from a simple built-in setting to a more powerful custom solution using DAX.

Why the Default Slicer Behavior Can Be Confusing

In Power BI, a slicer's default state is "All." If you haven't clicked on any specific option in a slicer, the visuals on your report page are showing data for every option. For example, if you have a slicer for "Country" and haven't selected anything, your charts will display data from all countries. The moment you select "USA," everything filters to show only data for the USA.

The confusion starts here. To get back to the "All" view, a user has to either click the small "Clear selections" eraser icon that appears on the slicer header or deselect the currently chosen option (by clicking it again, or Ctrl+clicking it). This isn't immediately obvious to everyone, especially business stakeholders who are used to web applications where "Select All" is an explicit option they can click.

An intuitive user experience (UX) is critical for report adoption. If navigating your dashboard feels clunky or confusing, users are less likely to engage with it. Providing a familiar "Select All" checkbox removes this friction and makes your report more accessible to a wider audience.

Method 1: The Quick and Easy Built-in Option

For many situations, Power BI's built-in "Select All" option is all you need. Microsoft added this feature to slicers, but it needs to be manually enabled. It's perfect for basic list or dropdown slicers and takes only a few seconds to set up.

Follow these steps to turn it on:

1. Select the slicer visual you want to modify on your report canvas.
2. In the Visualizations pane on the right, click the "Format your visual" icon (it looks like a paintbrush).
3. Expand the "Slicer settings" section. You may need to have your slicer set to "Vertical list" or "Dropdown" type for these options to appear.
4. Expand the "Selection" sub-section.
5. You'll see a toggle for "Show 'Select all' option". Turn this On.

Instantly, a new checkbox labeled "Select all" will appear at the top of your slicer list. Now, users can check this box to select everything or uncheck it to clear all selections. When an individual item is selected, the "Select all" checkbox automatically unchecks itself. This simple toggle provides the exact functionality most users are looking for and dramatically improves the slicer's usability.

When to Use This Method

This method is ideal for quick development and straightforward reports. If your main goal is simply to add that explicit "Select All" checkbox to a standard list slicer, this is the most efficient way to do it. However, it is tied to the standard slicer functionality and doesn't give you flexibility if you have more complex filtering logic in mind.

Method 2: Building a Custom "Select All" with DAX

Sometimes, the built-in option isn't enough. You might need more control over how "Select All" behaves, want it to apply in a non-standard visual, or have it interact with complex data models. For these scenarios, you can create a robust and flexible "Select All" feature using Data Analysis Expressions (DAX).

This approach involves creating a new, disconnected table for your slicer and then writing a measure to control the filtering logic. It sounds complicated, but we'll break it down step-by-step.

Step 1: Create a Disconnected Slicer Table

First, we need to create a new table that contains all the unique values from the column you want to filter, plus our own custom "Select All" entry.

Let's imagine our data model has a table named 'Sales', and we want a slicer for the 'Region' column.

1. Go to the Data view or Report view in Power BI.
2. In the Home tab, click "Enter data" to create a new table using DAX. Click "Load" on the dialogue that pops up. After the table is created, you can edit its definition.
3. Click on your new table, and enter the following DAX formula in the formula bar, replacing 'Sales'[Region] with your table and column:

RegionSlicerTable = 
UNION(
    ROW("Region", "Select All", "SortOrder", 0),
    SELECTCOLUMNS(
        VALUES('Sales'[Region]),
        "Region", [Region],
        "SortOrder", 1
    )
)

What Does This DAX Formula Do?

• ROW("Region", "Select All", "SortOrder", 0): This function creates a single-row table with columns 'Region' and 'SortOrder'. The row's 'Region' is "Select All," and its 'SortOrder' is 0. This ensures "Select All" appears at the top.
• VALUES('Sales'[Region]): Returns all unique regions from the 'Sales' table.
• SELECTCOLUMNS(...): Transforms this list into a two-column table with 'Region' and 'SortOrder' (set to 1 for all actual regions).
• UNION(...): Combines the "Select All" row with all the regions list.

After creating this table, sort the 'Region' column by the 'SortOrder' column in the Data view to position "Select All" at the top.

Step 2: Ensure the Table is Disconnected

Go to the Model view in Power BI. You should see your new table (e.g., 'RegionSlicerTable'). It must be disconnected from your main data table ('Sales'). Do not create a relationship between them. All filtering logic will be handled through DAX, not model relationships. Delete any auto-detected relationships if necessary.

Step 3: Create the DAX Measure for Filtering

Now, write a measure that determines what data to show based on the slicer selection.

1. Right-click on your 'Sales' table (or a dedicated Measures table) and select "New measure".
2. Enter the following DAX formula, adjusting table and column names:

Is Region Filtered = 
VAR SelectedRegion = SELECTEDVALUE('RegionSlicerTable'[Region])
RETURN
IF(
    HASONEVALUE('RegionSlicerTable'[Region]),
    IF(
        SelectedRegion = "Select All",
        1,
        IF(
            SELECTEDVALUE('Sales'[Region]) = SelectedRegion,
            1,
            0
        )
    ),
    1
)

How this Measure Works:

• VAR SelectedRegion = SELECTEDVALUE('RegionSlicerTable'[Region]): Gets the currently selected value in the slicer.
• HASONEVALUE('RegionSlicerTable'[Region]): Checks if the user has selected exactly one option.
• The IF conditions:

Step 4: Apply the Measure to Your Visuals

To make your visuals respond:

1. Add a slicer and assign it to 'Region' from 'RegionSlicerTable'. "Select All" will be among the options.
2. Select your visual (e.g., bar chart).
3. In the Filters pane for that visual, drag the measure 'Is Region Filtered'.
4. Set the filter to show items when the measure equals 1.

When users select "Select All," all data appears. Choosing a specific region filters accordingly.

Final step:

Apply this filter measure to all visuals to synchronize filtering through your custom slicer.

Final Thoughts

Providing an explicit "Select All" option greatly enhances report usability. For the quickest solution, enable the built-in toggle in slicer settings. For complex scenarios requiring precise control and customized behavior, the DAX approach offers a flexible and robust alternative.

Frankly, building and managing such reports in Power BI can be a full-time job of DAX troubleshooting and design. That’s why we created Graphed — to eliminate technical hurdles from data analysis. Connect all your marketing and sales sources in seconds, then create interactive, real-time dashboards by describing what you want in plain English. All the power of business intelligence, minus the steep learning curve.