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
For this example I have created data groups or bins for the Sales Amount for each order in $5,000 buckets.
I then created a card for Revenue across the top for each group.
I can click on any of the ranged revenue cards and see the rest of the report limited to only orders in that range:
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.
I also turn off all the other formatting options for a treemap, so it looks like a simple rectangle shape.
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”.
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”.
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.
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.
I also created a field that made a formatted name for my bins.
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:
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.