How do I filter results by a measure in Power BI

4
votes

I need to be able to filter by a measure to show more granular results in Power BI. I would like to be able to choose where the percentage is in a range, or = 100%

My calculation for the measure is this (each column can only be 1 or 0):

errorPercentage = CALCULATE(SUM([missing data])/SUM([expected]))

This works fine in the table and correctly shows the percentage which can be cut in many different ways.

However, when adding a filter element/visual, Power BI wont let me use the errorPercentage measure.

What is more confusing is that if I go to the Visual Level Filters section on the table I want to filter, I CAN use the errorPercentage measure to filter in a range or value.

Can anyone suggest an easy to use filter? I'm trying to let the users of the report choose a % range themselves, rather than having a pre-defined one.

2
filterpowerbidax
just to clarify: it seems to me that you want to aggregate data, slice it on varying dimensions, and then use a slider or other visual to filter out those slices that don't aggregate to the desired range of 'errorPercentage' -- is that right? – Ryan B.
Yes that's right. The weird thing is that I can do this in the report level filters where Measure = 100%. But I cant do it in user controlled visual filters. That doesn't really make sense to me. – drcoding

2 Answers

4
votes

You can't use an aggregated measure as a slicer. A slicer filters a dataset to all tuples that participate in the selected values of a given dimension. A measure is not a dimension. It cannot be used to identify subsets of records.

Suppose I had data on basketball players and I wanted to slice to the set of players whose 'Average Free throw percentage' was 85%. Think about all of the different subsets of players who would have such an average! Maybe we have "Player A" with 90 percent who could be matched with one "Player B" who scores 80 percent of his free throws -- they average 85% (assuming that they have taken the same number of total throws each). Or, "Player A" could be grouped with 5 other players whose percent is 84 percent. But those same 5 players wouldn't be part of the set without "Player A" - so are they or aren't they part of the desired subset? It's ambiguous.

0
votes

You can create a calculated column that specifies where each value falls within a % range using a simple switch calculation. Once this column exists you can use it as a slicer; if the data is not contiguous you can always create a table that encompasses the entire range of values and join on that column and use that table as a slicer.