Forum Discussion
Filtering rows out that are less or greater than a defined range/value
I have a dataset with dates, text and numbers. I created a table visual with just the text and numbers. I set the columns to display the numbers as averages. I have a date splicer which I use to see data between certain date ranges. As I adjust the date ranges, the averages/values in the number column change as expected since only data in the specified date range is fed to the report/visual.
I am looking for a way to filter out rows in the table that have an average below a specified value. I want to do this without affecting the underlying dataset or the data being fed to the report. In other words, if the average over a specified date range is lower than x, filter out the two. I tried to use a splicer to do this but it does not work since it filters at the dataset level and therefore filters out raw data with a value lower than x, which then affects the averages since the average is being calculated with different values.
Any idea how I can achieve what I want to achieve?
Hi anonymoose
One method is to
- Create a disconnected parameter table to use on the pct_available range slicer
- Create a "flag" measure that returns 1 only if the measure value is within the range selected.
- Use the flag measure as a visual-level filter, set equal to 1.
I have attached a small example:
- Disconnected table pct_available contains a single column with values 0.00, 0.01, ..., 0.99, 1.00 (formatted as percentages).
- Created measures as show below:
Average pct_available = AVERAGE ( Data[pct_available] )Flag pct_available in range = // Returns 1 if [Average pct_available] is in range VAR MinPct = MIN ( pct_available[pct_available] ) VAR MaxPct = MAX ( pct_available[pct_available] ) VAR CurrentPct = [Average pct_available] RETURN INT ( CurrentPct >= MinPct && CurrentPct <= MaxPct )3. Add the measure [Flag pct_available in range] as a visual-level filter set equal to 1. This will work regardless of which field(s) the table is grouped by.
Does something similar work for you?
Another method is to bake the filtering into the measure displayed in the table itself, for example:
Average pct_available filtered = VAR pct = [Average pct_available] VAR MinPct = MIN ( pct_available[pct_available] ) VAR MaxPct = MAX ( pct_available[pct_available] ) RETURN IF ( pct >= MinPct && pct <= MaxPct, pct )This particular measure may need tweaking to handle the total in an appropriate way.
Here's a blog post with a similar example:
https://datasavvy.me/2024/05/01/use-a-slicer-to-filter-a-visual-based-upon-a-measure-in-power-bi/
At least these are some ideas to start with.
Regards
1 Reply
- OwenAuger
Super User
Hi anonymoose
One method is to
- Create a disconnected parameter table to use on the pct_available range slicer
- Create a "flag" measure that returns 1 only if the measure value is within the range selected.
- Use the flag measure as a visual-level filter, set equal to 1.
I have attached a small example:
- Disconnected table pct_available contains a single column with values 0.00, 0.01, ..., 0.99, 1.00 (formatted as percentages).
- Created measures as show below:
Average pct_available = AVERAGE ( Data[pct_available] )Flag pct_available in range = // Returns 1 if [Average pct_available] is in range VAR MinPct = MIN ( pct_available[pct_available] ) VAR MaxPct = MAX ( pct_available[pct_available] ) VAR CurrentPct = [Average pct_available] RETURN INT ( CurrentPct >= MinPct && CurrentPct <= MaxPct )3. Add the measure [Flag pct_available in range] as a visual-level filter set equal to 1. This will work regardless of which field(s) the table is grouped by.
Does something similar work for you?
Another method is to bake the filtering into the measure displayed in the table itself, for example:
Average pct_available filtered = VAR pct = [Average pct_available] VAR MinPct = MIN ( pct_available[pct_available] ) VAR MaxPct = MAX ( pct_available[pct_available] ) RETURN IF ( pct >= MinPct && pct <= MaxPct, pct )This particular measure may need tweaking to handle the total in an appropriate way.
Here's a blog post with a similar example:
https://datasavvy.me/2024/05/01/use-a-slicer-to-filter-a-visual-based-upon-a-measure-in-power-bi/
At least these are some ideas to start with.
Regards