Do you want to select all values in a Power BI slicer?

Filtering data in Power BI using slicers is a great way to narrow down the information you need for your analysis. However, selecting all the values in a slicer can be a tedious and time-consuming task, especially when working with large datasets.

Fortunately, there is a simple solution that enables you to select all the values in a slicer with just a few clicks. In this blog post, you will learn the process of selecting all values in a Power BI slicer, so you can optimize your data analysis and get more insights from your data in less time.

Select All Values in Power BI with Clear Selections

If you have a slicer with several items selected, you can choose from two options to select all items in the slicer. The first option is to select the unselected items by using the Ctrl+ right-click method. However, this approach can be time-consuming if there are many categories or groups in the slicer.

The second option is to use the Clear selections button to deselect everything on the slicer, which will effectively select all items.

After creating a slicer, locate the Clear selections button by hovering your mouse over the slicer visual. Look for the eraser-like icon located in the top right-hand corner of the slicer and click on it.

Clicking on the Clear selections icon will remove the checkmarks from all categories. As shown in the image above, selecting all categories yields the same result as selecting none, even after the selections have been cleared.

When you clear the selection in a Power BI visual, all filters are removed, and the visual will display the values without any filters applied. Conversely, when you select all the filter categories, the visual will display the value of each category, as well as the total sum of all values in the categories. This will appear as though no filters have been applied, providing an unfiltered view of the data.

Select All Values in Power BI with DAX

For this method, you will create a DAX measure that you can add to your slicer so that it will display the specific category that is selected. This is a nice visual that can be useful for providing context about which categories you have selected in a slicer.

When you have this visual in your report, it will display the selected categories clearly and concisely so that you or others using the report can quickly and easily see the category/categories being used in the analysis, without having to search through a list of all the available categories.

You need to create a DAX measure to create this visual. To do this, go to the Home tab and select New measure.

Selected Category = 
CONCATENATEX(
    ALLSELECTED(
        Products[Category]
    ),
    Products[Category],
    " & "
)

Copy and paste this syntax in the formula bar. Press the Enter key after pasting the syntax. This code creates a measure called “Selected Category” that concatenates the names of the categories that have been selected in the slicer.

Here’s how it works:

The CONCATENATEX function takes a table and evaluates an expression on the table to return the names of the selected categories into a single string. The function takes the following arguments:

• <Table> – the table on which the expression will be evaluated. Because the CONCATENATEX function is an iterator function, the expression will evaluate on each row.
• <Expression> – the formula that will be evaluated for each row in the <Table>.
• [<Delimiter>] – the delimiter that will be concatenated alongside the <Expression>.
• [<OrderBy_Expression>] – the formula that will determine the sort order for the <Table>.
• [<Order>] – the sort order. The argument uses Boolean values of 0/FALSE/DESC to sort in descending order, or 1/TRUE/ASC to sort in ascending order.

You only need the <Table> and <Expression> arguments for the CONCATENATEX function as the other arguments are optional.

In the DAX syntax above, the ALLSELECTED(Products[Category]) creates a virtual table that retrieves values from the Products[Category] column. Because the ALLSELECTED function is used to create this table, filters that come from the Products[Category] column in the present query are ignored while filters from other external contexts or queries are evaluated.

The second argument returns the values in the Product[Category] column created with the ALLSELECTED function. The third argument sets the ampersand symbol ‘&’ as the delimiter to use between the values.

Create a card using the Selected Category measure and create a slicer using the Category column. When you select any categories from the slicer, the card visual will display the name of the selected category.

In the image above, the Audio category is selected, and it’s displayed on the card visual. This also filters the table with the Category and Expenses visual to only reveal details for the Audio products category.

Likewise, when you select more than one category, the card updates to show the name of the categories selected.

When no category is selected or no filter is applied, then all categories will be displayed on the card. This can create the card to become overpopulated with many categories.

Selected Category = 
IF(
    NOT(
        ISFILTERED(
            Products[Category]
        )
    ), "All Categories",
    CONCATENATEX(
        ALLSELECTED(
        Products[Category]
        ),
        Products[Category],
        " & "
    )
)

You can manage this situation by adding an IF conditional statement to the “Selected Categories” DAX measure.

Like every other IF statement, this syntax checks whether the logical test is true or false. The logical test is contained using the NOT(ISFILTERED(Products[Category]) syntax. Essentially, what this does is check whether the Products[Category] column is not filtered.

When this test returns true, then the IF function will return “All Categories“, so that this will be the value that will display on the card visual. When the logical test returns false, it runs the CONCATENATEX part of the syntax and returns the selected category.

Now, when you remove filters from the slicer, the card visual will return “All Categories”, instead of a list of all the categories from the Products[Categories] column.

A further option you have is to adjust the measure in such a way that, when a particular number of categories are selected, the card presents the count of selected categories instead of listing out the names of those categories. This alternative approach enables you to display the card’s value more succinctly.

Selected Category = 
IF(
    NOT(
        ISFILTERED(
            Products[Category]
        )
    ), "All Categories",
    If(
        COUNTROWS(
            ALLSELECTED(
                Products[Category]
            )
        ) > 3,
        "Four or more Categories",
    CONCATENATEX(
        ALLSELECTED(
        Products[Category]
        ),
        Products[Category],
        " & "
    )
))

To update the Selected Category slicer, you need to add a new IF statement. The IF statement checks if the number of selected values in the Products[Category] column is greater than three using the syntax COUNTROWS(ALLSELECTED(Products[Category])) > 3.

If this condition is true, the visual will display “Four or more Categories”. Otherwise, the CONCATENATEX function will run and return the selected value from the categories.

The second IF statement is nested in the FALSE argument of the first IF statement. This means that if the first IF statement returns FALSE, the second IF statement will be executed.

Conclusion

Selecting all values in a Power BI report can be a time-saving and efficient way to analyze large datasets. With the methods described in this post, you can easily select all values in a slicer or remove all filters in a visualization.

In addition, implementing the DAX syntaxes outlined in this post can optimize your report by facilitating the identification of the specific groups or categories of data being represented.

By utilizing this method, you can produce a clear and succinct report that is easily comprehensible for all users.