Card visualizations in Power BI highlight summarized data in an eye-catching way. If you are not familiar with cards, they are typically used to display, in larger-sized font, aggregated segments of an underlying data set, e.g., subtotals, averages, counts. The following example illustrates typical card visualizations which, in this case, show total quantities from the sample data set by device type. In my experience, users almost always attempt to click on a card visualization expecting it to filter the overall report. When I eventually receive user feedback that “the report doesn’t work”, I usually respond with a joke that “it’s not in the cards”. The user then stares at me blankly in awkward silence instead of providing the expected laughter. Rinse and repeat.
I hope you understood the joke since you’re reading this article. However, just in case, I’ll explain it anyway because all great jokes require an explanation (/sarcasm). Card visualizations, unlike most other visualizations in Power BI, cannot be used to filter a report. Accommodating user expectations for clickable and filterable cards requires some trickery with bookmarks and shapes. This article describes the basic steps to achieve filterable card visualizations.
Step 1 – Data Set Up
The sample data used for this guide contains a few categories of devices with the quantities of each device by month. The Power BI report will include several cards displaying the overall device total as well as subtotals by device type.
Power Query is used to retrieve, filter, and transform the data from Excel into the unpivoted columns needed for the card visualizations in Power BI. The Power Query steps are provided 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 structure with columns for device type, month, and quantity is displayed below along with the transformed data.
Step 2 – Creating Card Visualizations
The following steps can be used to create each card visualization. To avoid unnecessary repetition, only the steps used to create the card visualization for the desktops subtotal are shown.
From the Visualizations pane, click the Card visualization to add it to the report. Next, add the QUANTITY field from the Fields pane to the Fields section of the card visualization. The card now displays the total across all devices. Since this will become the desktops card, we will need to add a device filter.
With the newly created card visualization still selected, open the Filters pane and add the DEVICE field from the Fields pane to the Filters on this visual section of the Filters pane. Expand the DEVICE filter and select the Desktops value. The card now displays the total number of desktops.
After creating each of the required card visualizations, the report appears as follows. I also added a simple Clustered Column Chart visualization and a Matrix visualization to both verify the data summation as well as the eventual filters.
Step 3 – Creating Bookmarks
If the Bookmarks pane is not visible, then go to the View ribbon and activate Bookmarks from the Show panes section.
NOTE: Before we create the bookmarks, please note that Power BI bookmarks act as a snapshot of the report filters and visuals at the time the bookmark is created. If you create a bookmark while the Filters pane is expanded, then the Filters pane will be opened when the bookmark is activated by the user. When setting a bookmark, please make sure that the report is in the exact state that a user expects to see when the bookmark is activated. If changes are made to the report after the bookmarks are created, then you may need to update the bookmarks to reflect structural changes.
The first bookmark we’ll create is the DEFAULT bookmark. This bookmark is used to reset all filters back to their default state as if the user first opened the report.
Once the report is in its expected default state where all filters are set/unset as expected, the display and panes are in their expected default states, and all visuals are in their expected default states, then go to the Bookmarks pane and click the Add button. Click the ellipsis (…) next to the newly created bookmark and select Rename. Set the bookmark name to DEFAULT.
The second bookmark we’ll create is the DESKTOPS bookmark. This bookmark is used to filter the report for all desktop devices.
Reset all report filters, visuals, etc. back to their default states. Open the Filters pane. In the Filters on this page section, add the DEVICE field. Expand the DEVICE page filter and select the Desktops value. Close the Filters pane. Go to the Bookmarks pane and click the Add button. Click the ellipsis (…) next to the newly created bookmark and select Rename. Set the bookmark name to DESKTOPS.
Repeat those steps to create bookmarks for the laptops and peripherals device types.
Step 4 – Insert and Format Transparent Shapes
At this point, we have the card visualizations and bookmarks representing each of the filters. However, we need a way for the user to directly activate those bookmarks by clicking on a card visualization. Since we can’t add bookmarks to a card visualization, we need to create a transparent shape that is overlaid on the card.
From the Insert ribbon, in the Elements section click Shapes and select one of the Rectangles options. Select the rectangular shape that was added to the report. On the Format pane, click the Shape tab and expand the Action section. Activate the Action option. Select Bookmark from the Type drop-down menu. Select the DEFAULT bookmark from the Bookmark drop-down menu. Unless it is needed, deactivate the Tooltip option.
Resize the shape and position it over the total card visualization. In this example, the shape had a default blue fill color which I have temporarily left active for illustration.
Let’s remove the fill color so that the underlying card is visible. Select the rectangular shape overlay. On the Format pane, click the Shape tab and expand the Style section. Deactivate the Fill, Border, Text, Shadow, and Glow options.
The rectangular shape is now transparent and the underlying card visualization is visible. Repeat these steps for each of the card visualizations that will be clickable for the user.
Result
Clicking on the desktops card, which is really the transparent shape overlay, results in the activation of the DESKTOPS bookmark and the report visualizations are filtered for desktops only. Clicking on the total card, results in the activation of the DEFAULT bookmark and the filters are cleared. From the user’s perspective, it appears that clicking on the card filters the overall report data and the report works as expected.
