Filter out a Dimension by using a Measure as the Threshold Value

+filter +threshold +intermediate +multipass

In many scenarios we need to filter data based on critical threshold values. For that purpose we have a variety of ways of using the default filter mechanism. Here we want to look at a case where I dont just want to remove the data points that exceed/fall below a condition, I want to remove the entire dimension value that is related to those data points.

The basic scenario :: if I have a spread of values between 0-100 and I want to filter everything over 50, thats a simple setting. If I started off with 3 lines, I will continue with 3 lines but all values over 50 will be filtered out.

The advanced scenario :: if I have a spread of values between 0-100 and I want to remove the complete lines on a line chart when any of its values are over 50. If I started off with 3 lines and 2 of them have values over 50, I want to filter out those entire lines from my visual, leaving me with 1 line.

The problem statement here requires two queries to complete. The first is what are the values of all the points for my measures and dimensions. The second query is based on those results I want to find all values that exceed/fall below a condition (using my measures as a threshold) and then find the related dimension plot on the visual and remove it.

So lets begin with a new visual

We’ll use some sales data to make our case, using orderDate, region and sales. We want to filter out a complete region, when its values break the threshold condition in our resulting visual. This is our first query.

Now we will make a LOGICAL VIEW to base our second query on, from the results of the first.

Lets use some practical names in this case and find a place to store our new asset.

When the VIEW is completed being created, you can use the link on the screen to go directly to a new Dashboard and Visual based on your first query.

Now we have a new Dashboard with a Dataset based on our initial query.

Lets go ahead and EDIT the visual.

From within the DATA panel you will find your shelves and one shelf called FILTERS. Adjacent to this shelf is a BUTTON that allows us to build a NEW SEGMENT.

12

17

The condition or threshold we want to set is such - when any value as part of the dimension LINE [region] exceeds 50,000 in sum of sales, I want to keep all the values on that LINE and no others.

With your new segment saved. We can go ahead and change the visual type to LINE chart and add our order date to the dimension shelf, sum of sales to the measures shelf and region to the colour shelf.

Right now, there is no condition applied so we see all the values across the dataset. Lets go ahead and apply our new segment to give us our threshold results only. Use the fly-panel icon labelled SEGMENTS. Choose your threshold condition.

So now we have evaluated the measure values and used those as a THRESHOLD trigger to filter out dimension values from our visual.

End of article.

2 Likes

Thanks, that’s a good example of entity based segments.

The segment filters and extracts only the entities (regions) that’s desired. Then a self join is performed using those entity (region) to plot over time. Looking at the underlying SQL helps show what’s happening.

I wonder if Derived Data could have been used as well.