Power BI: Filter Using Card Visualizations and Bookmarks

Card visualizations in Power BI display summarized data in an eye-catching way. Cards are typically used to show aggregated segments of a dataset (e.g., subtotals, averages, counts) in a larger font. The following example illustrates card visualizations that show total quantities by device type from the sample dataset.

In my experience, users often try to click on a card, expecting it to filter the report. When I receive feedback like “the report doesn’t work,” I usually joke, “it’s not in the cards.” The user then stares at me in awkward silence instead of laughing. Rinse and repeat.

I hope you got the joke since you’re reading this article. But just in case, I’ll explain it — because all great jokes need an explanation (/sarcasm). Unlike most other visualizations in Power BI, card visuals can’t be used to filter a report. To make cards filterable, you’ll need to use bookmarks and shapes. This article explains the basic steps to create filterable card visualizations.

Instructions

Step 1: Data Set Up

The sample data in this guide includes device categories with monthly quantities. The Power BI report will feature several cards showing the total device count and subtotals by device type.

Power Query is used to retrieve, filter, and transform the data from Excel into the unpivoted columns required for the card visualizations in Power BI. The Power Query steps are listed below for reference.

let
  Source = Excel.Workbook(File.Contents("C:\report.xlsx"), null, true),
  Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
  #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
  #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers", {{"Column1", "DEVICE"}}),
  #"Filtered Rows" = Table.SelectRows(
    #"Renamed Columns",
    each ([DEVICE] <> null and [DEVICE] <> "Total")
  ),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Filtered Rows",
    {"DEVICE"},
    "Attribute",
    "Value"
  ),
  #"Renamed Columns1" = Table.RenameColumns(
    #"Unpivoted Other Columns",
    {{"Attribute", "MONTH"}, {"Value", "QUANTITY"}}
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Renamed Columns1",
    {{"DEVICE", type text}, {"MONTH", Int64.Type}, {"QUANTITY", Int64.Type}}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", {{"MONTH", type date}})
in
  #"Changed Type1"

The resulting Power BI table, with columns for device type, month, and quantity, is shown below along with the transformed data.

Step 2: Creating Card Visualizations

Follow these steps to create each card visualization. To avoid repetition, only the steps for creating the desktops subtotal card are shown.

1. From the Visualizations pane, click the Card visualization to add it to the report.
2. In the Data pane, add the QUANTITY field to the card visualization’s Fields section. The card will now display the total for all devices.

3. With the new card visualization selected, open the Filters pane and add the DEVICE field to the Filters on this visual section. Expand the DEVICE filter and select the Desktops value. The card will now display the total number of desktops.

After creating the required card visualizations, the report will appear as shown below. I also added a simple Clustered Column Chart and a Matrix visualization to verify the data summation and filters.

Step 3: Creating Bookmarks

If the Bookmarks pane is not visible, go to the View ribbon and enable Bookmarks from the Show panes section.

NOTE: Power BI bookmarks capture a snapshot of the report’s filters and visuals at the time they are created. If the Filters pane is expanded when a bookmark is created, it will be open when the bookmark is activated. Make sure the report is in the exact state you want users to see when the bookmark is activated. If changes are made to the report after creating the bookmarks, you may need to update them to reflect those changes.

The first bookmark we’ll create is the DEFAULT bookmark, which resets all filters to their default state, as if the user has just opened the report.

1. Once the report is in the desired default state (with all filters set/unset and visuals in the expected layout), go to the Bookmarks pane and click the Add button.
2. Click the ellipsis … next to the new bookmark and select Rename. Name the bookmark DEFAULT.

The second bookmark is the DESKTOPS bookmark, which filters the report to show only desktop devices.

1. First, reset all filters and visuals to their default states.
2. Open the Filters pane.
3. In the Filters on this page section, add the DEVICE field.
4. Expand the DEVICE filter and select Desktops.
5. Close the Filters pane.
6. Go to the Bookmarks pane, click Add, then click the ellipsis … next to the new bookmark and select Rename. Name it DESKTOPS.
7. Repeat these steps to create bookmarks for the laptops and peripherals device types.

Step 4: Insert and Format Transparent Shapes

At this point, we have card visualizations and bookmarks for each filter. However, we need a way for users to activate those bookmarks by clicking on a card. Since we can’t add bookmarks directly to a card, we’ll overlay a transparent shape on top of the card.

1. From the Insert ribbon, in the Elements section, click Shapes and choose a rectangle option.
2. Select the newly added rectangle and go to the Format pane. Click the Shape tab and expand the Action section.
3. Enable the Action option, then choose Bookmark from the Type drop-down menu. Select the DEFAULT bookmark from the Bookmark drop-down menu. If not needed, disable the Tooltip option.

4. Resize the shape and position it over the total card visualization. The shape may have a default blue fill, which is left for illustration here.

5. To make the card visible, remove the fill color. Select the shape, go to the Format pane, click the Shape tab, and expand the Style section. Disable the Fill, Border, Text, Shadow, and Glow options.

Now, the shape is transparent, and the card visualization is visible. Repeat these steps for each card visualization that should be clickable.

Results

Clicking on the desktops card (which is the transparent shape overlay) activates the DESKTOPS bookmark, filtering the report to show only desktops. Clicking on the total card activates the DEFAULT bookmark, clearing the filters. From the user’s perspective, it looks like clicking on the card filters the report data, and everything works as expected.

Summary

By using bookmarks and transparent shapes, you can create interactive card visualizations in Power BI that allow users to filter reports by simply clicking on a card. This approach enhances the user experience, making the report feel more responsive and intuitive while ensuring the report behaves as expected when users interact with the cards.