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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Nate_Witkin
Frequent Visitor

How to Filter a Visualization to Display Only Data Similar to a Selection (Disconnected Tables)

Hello,

 

I am trying to create a dashboard that allows users to select an observation (for example, a store location) and automatically produce a bar chart displaying only similar observations (for example, the individual sales of other store locations with similar sales). To clarify, I don't want to aggregate results across the similar observations -- instead, I want to display the individual results of each similar observation, based on a dynamic selection of an observation to be compared.

 

@Ashish_Mathur created a very helpful solution to a related question, producing a concatenated list of the names of similar stores, based on (1) selecting a store in a slicer and (2) selecting a range of variation from a disconnected table. His pbix file is attached to this earlier thread. It may be possible to use the CONTAINSSTRING function to filter the data table only to the rows with store locations identified by his code as similar (through the concatenated list), but I have not figured out how to do it. I've also tried multiple disconnected tables, but I'm struggling with that as well.

 

Basically, how can a Power BI user select an observation and have a visualization display not only that selection, but also the observations that are most similar to that observation? This would be very helpful for helping clients compare apples-to-apples within a large, diverse dataset.

 

I would greatly appreciate any suggestions.

 

Best,

Nate

3 REPLIES 3
Nate_Witkin
Frequent Visitor

By stores with "similar" sales, I mean stores with sales that are plus or minus X% from the sales of the store selected. This selection problem was solved by Mr. Mathur, referenced above, by using a disconnected table of percentages and then using that disconnected table to create measures that are used as filters in a calculate function. So, a user could click on a store, then select a range (+/-X%) to apply to the sales, and find the stores whose sales are within that range -- Mr. Mathur did so with a concatenated list; I was looking for a bar chart.

 

Basically, my problem here is that Power BI does not allow measures to dynamically filter slicers. For example, if I have measures defining a range of values (+5% and -5% of a selected store's sales), I cannot feed these into slicers to automatically filter the rest of the page. My best solution was to have cards displaying the range values generated above the slicer, so the user could define a range and then plug the resulting values into the slicers themselves.

Basically, my problem here is that Power BI does not allow measures to dynamically filter slicers.

That is mostly true, but not always. You can use measures as visual level filters in certain circumstances.

 

However your scenario seems to be better served by educating your users on how to use the basic Power BI filtering functionality, by promoting the use of the filter pane, and by enabling the "Personalize visuals"  option.

lbendlin
Super User
Super User

Can you define what "similar"  means in this scenario? Can you give examples?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.