Help with Dynamically Filtering Details Based on Selected Cell in Summary Table

Power BI
1

Hi Everyone,
I’m looking for guidance on achieving a dynamic interaction in Power BI, and I’m unsure if it’s fully possible with the current Power BI capabilities.

Here’s what I’m aiming for: I want to set up a report where, if a user selects a cell in a summary table, the details table below will filter to show only the relevant transactions tied to that specific selection. This is especially important for measures in the table, where I need to identify both the column and measure the user selected.

For instance:

  • If the user clicks on the cell containing “Electronics,” I want to capture that they’ve selected the “Product Category” column.

  • image

  • More crucially, if the selection is on a measure (e.g., the cell showing the value “26” for transactions with a discount), I need the details table to display only those 26 related transactions.

  • image

I’m not sure if I’m approaching this the right way, so any guidance on how to identify the selected cell’s column (for fields and measures) or on an alternative method to achieve this dynamic filtering would be appreciated!

Thanks for any help you can provide!
financial_dataset.csv (67.5 KB)
financial_dataset.pbix (70.2 KB)

2

HI @Roberto

Have you tried using DataMentor to help you solve your issue?

Please give it a try to help you.

thanks
Keith

3

Hi @Keith,
I tried, but explaining the problem is not at all simple. I’m only getting common-sense suggestions that I’ve already tried to implement.

Thanks,
roberto

4

@Roberto, do you have to do this in tables?

When you select a cell in a table you don’t actually select a single cell but rather the entire row on which that cell exists. You can see this in action if you place a measure like

FiteredCols = 
VAR MaxFilters = 3
RETURN
IF ( 
    ISFILTERED ( financial_dataset[Customer] ), 
    VAR ___f = FILTERS ( financial_dataset[Customer] ) 
    VAR ___r = COUNTROWS ( ___f ) 
    VAR ___t = TOPN ( MaxFilters, ___f, financial_dataset[Customer] )
    VAR ___d = CONCATENATEX ( ___t, financial_dataset[Customer], ", " )
    VAR ___x = "financial_dataset[Customer] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " " 
    RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
 & IF ( 
    ISFILTERED ( financial_dataset[Product] ), 
    VAR ___f = FILTERS ( financial_dataset[Product] ) 
    VAR ___r = COUNTROWS ( ___f ) 
    VAR ___t = TOPN ( MaxFilters, ___f, financial_dataset[Product] )
    VAR ___d = CONCATENATEX ( ___t, financial_dataset[Product], ", " )
    VAR ___x = "financial_dataset[Product] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " " 
    RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
 & IF ( 
    ISFILTERED ( financial_dataset[Product Category] ), 
    VAR ___f = FILTERS ( financial_dataset[Product Category] ) 
    VAR ___r = COUNTROWS ( ___f ) 
    VAR ___t = TOPN ( MaxFilters, ___f, financial_dataset[Product Category] )
    VAR ___d = CONCATENATEX ( ___t, financial_dataset[Product Category], ", " )
    VAR ___x = "financial_dataset[Product Category] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " " 
    RETURN ___x & UNICHAR(13) & UNICHAR(10)
)

in a table next to your summary table:

image
image926×283 18.2 KB

You see the FilterCols table (green box on the right) doesn’t distinguish which particular column was clicked (I clicked on the cell in the red box). This is how table visuals handle interactions in Power BI. The entire row is selected (green box, left).

Even though Power BI doesn’t natively support cell-level interactions, it’s possible to get close to what you want to do with core visuals and some elbow grease. You might consider using Vega.

3 Likes