Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

DataZoe

Creating a clickable card to filter a report

For this example I have created data groups or bins for the Sales Amount for each order in $5,000 buckets.

 

DataZoe_0-1619142832628.png

 

DataZoe_1-1619142844164.png

I then created a card for Revenue across the top for each group.

 

DataZoe_0-1619142259037.png

 

I can click on any of the ranged revenue cards and see the rest of the report limited to only orders in that range:

Clickable cards.gif

To do this I insert a treemap visual, with the measure and the Bin column as the treemap Group. I then filter the treemap in the filter pane to just the first card, < $5k.

 

DataZoe_1-1619142303011.png

 

 

I also turn off all the other formatting options for a treemap, so it looks like a simple rectangle shape.

DataZoe_2-1619142303013.png

 

I create a transparent color measure:

 

Transparent = "#FFFFFF00"

 

And for the treemap, in “Formatting” > “Data colors” I click on the “Advanced controls”. Change this to “Format by” to be “Field value” and “Based on field” to be the measure we just created, “Transparent” and click “OK”.

DataZoe_3-1619142303019.png

 

Now you should see the card behind the treemap visual, but you can now click and it will filter. I then copy the treemap and adjusted for each of the other cards.

 

A note on cross-filtering vs cross-highlighting

For this to work as a filter instead of highlighting, you also should change the default behavior to cross filtering (instead of highlighting). You can do this by going to File > Options and Settings > Options > Report Settings (all the way at the bottom in the CURRENT FILE section) and check the box for “Change default visual interaction from cross highlighting to cross filtering”.

DataZoe_4-1619142303038.png

 

A note on including a tooltip to help the user 

Another consideration for this approach is you have to click the same card again to un-filter. I like to include a tooltip page so when the user hovers over the cards it lets them know about this functionality.

Clickable Cards Tooltip.gif

 

A note on showing when it is filtered

You can also include a text box on the page that shows when the user has filtered the dataset using the clickable cards. I did this at the top right of my page, and used the Quick Measure to write most of the DAX for me, then I added in some conditions on when it shows.

DataZoe_5-1619142437489.png

 

 

I also created a field that made a formatted name for my bins.

DataZoe_6-1619142437490.png

 

Sales Bins Labels = SWITCH([Sales (bins)],0,">$0 & <= $5,000", 5000, ">$5,000 & <= $10,000",10000,">$10,000 & <= $15,000",15000,">$15,000 & <= $20,000",">$20,000")

 

List of Sales Bins values =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Orders'[Sales Bins Labels])

VAR __MAX_VALUES_TO_SHOW = 6

RETURN

if(__DISTINCT_VALUES_COUNT>2,blank(),

"Currently showing orders with sales amounts " &

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW,

                    VALUES('Orders'[Sales Bins Labels]),

                    'Orders'[Sales Bins Labels],

                    ASC

                ),

                'Orders'[Sales Bins Labels],

                ", ",

                'Orders'[Sales Bins Labels],

                ASC

            ),

            ", etc."

        ),

        CONCATENATEX(

            VALUES('Orders'[Sales Bins Labels]),

            'Orders'[Sales Bins Labels],

            ", ",

            'Orders'[Sales Bins Labels],

            ASC

        )

    ))

 

 

I then used the text box to include it, by assigning a value:

DataZoe_7-1619142437495.png

 

A note on combining groups

If you want to combine two filters in one card, like I did with the >$15k (combining >15k and >20k), you will need to consolidate those into a single group to add to the treemap.

 

I did it with this calculated column, which I then used in the Group on the Treemap and in the Filter pane:

 

Sales (bins) > 15k = if(Orders[Sales (bins)] >=15000, 1,BLANK())

 

Hope this helps you in your reports!