Slicer based on measure for two visuals

[SamTrexler]

I have two visuals showing different perspectives of a measure called Fail Percent. The first visual is a table showing the Fail Percent for each location, and the second visual is a map showing the Fail Percent by County. I can use the Visual Level Filter on each visual to restrict what's shown based on the value of the measure, e.g., only locations or counties where Fail Percent > 7%.

However:

1. Each visual must be filtered separately, and
2. Once I place this in the Service, filtering should be available on a dashboard as a live pinned page, for C-level executives to quickly evaluate.

Since filters are not available on a live pinned page, and it's too messy for the target audience to quickly and reliably get the view they need, I am trying to create a slicer that will filter both visuals. One of the new numeric sliders would be awesome.

But a slicer cannot contain a measure. Is there a way to make a slicer that emulates what the Visual Level Filter does, based on the value or range of a measure?

Based on some posts on the subject, I've created a datatable with the percentage values (0-1, formatted as a percent) and placed that in a slicer. Then I make the measure return a value only if the Fail Percent is between the MIN and MAX of that table, like this:

Fail Percent (FPct) = var LowVal = [MinRangeValue] var HighVal = [MaxRangeValue] var FailPct = [Fail Count]/[Total Test Count] return IF(AND(FailPct>=LowVal,FailPct<=HighVal),FailPct)

This only shows a value if it's within the range, as a CALCULATE does if I specify the same condition there. But it also has the effect of not showing the row if the condition is not met. (CALCULATE shows the row, with no value in this column.) I need it to filter the rows shown, since there are about 10,000 locations and this visual should quickly limit the visual down to those few dozen "hot" locations with high Fail Percentages.  So this is the best solution I've found so far.

But there are several problems with this approach:

1. Totals and subtotals do not honor the "filtering" applied underneath them. For example, If Region 1 consists of 20 locations and I "filter" on a Fail Percent range that causes only 3 locations to be displayed, the Region 1 total row still includes the results of all 20 locations. (The Visual Level Filter summarizes the displayed rows.)
2. Totals and subtotals do not display if the Fail Percent for all locations in that level does not meet the condition. So, if I use a New Matrix visual, none of Region 1 will display if the overall Fail Percent for that region - including all rows, displayed or not) does not meet the condition. So for example, if Region 1 has an overall Fail Percent of just 5% it won't be selected and none of its rows will be displayed - even if they individually have Fail Percents well within the selected range. (By contrast, the Visual Level Filter recalculates the Region toal based only on the selected rows. If the Visual Level Filter is set to "greater than .07", then only rows with Fail Percent > 7% are included, and the Region total is higher - around 11% or 12% in this case - so it is displayed as are the qualifying rows under it.) I need the selection to iterate over the indivdual rows, and not be overriden by the higher level not applying the selection. (I'm using a table instead of a matrix to avoid this, but now I don't even get a grand total so the visual is not as useful.)
3. Other visuals don't show the effect of the "filtering" applied by the slicer. So, for example, the count of locations doesn't change based on the selection, nor does the overall count of events, failures or Fail Percent.

All of this works as I need it to if I use Visual Level Filters on each visual, but that is too cumbersome and is not available on a pinned live page in Service.

Is there a way to make a slicer that emulates what the Visual Level Filter does, based on a measure?

Thanks for your help.

[v-huizhn-msft]

Hi @SamTrexler,

You can create a calculated column, then select it as slicer. For example, if you want to filter to rows where the percentage is less than 7% or not. Please create a calculated column using the formula.

 > 7%=IF(Table[Percentage]>7%,"Yes","No")

Create a slicer including [> 7%], when you select "Yes", it returns all the rows bigger than 7%, otherwise, the result is converse.

Best Regards,
Angelia

[SamTrexler]

Hi @v-huizhn-msft,

Thanks for the suggestion, I'll see if I can make it work. I generally don't create calculated percentages in a table because they don't summarize correctly. I create percentages as a measure, where the sum of the numerator column and the sum of the denominator column are cacluated based on the relevant filters and then used to calculate the percentage for those filters (e.g., summary level). I'll see if I can make it work in this case.

Regards,

Sam

[SamTrexler]

@v-huizhn-msft, I'm unable to avoid a circular reference if I create the percentage as a calculated column. Is there a trick to do this?

Consider a very simplified version of my SQL Server table:

RegionID         varchar(10)

CountyName  varchar(35)

StationID         varchar(10)

Status              varchar(5)

EventCount        number

A StationID beongs to a RegionID and a CountyName, and for each StationID there is a row with the value of EventCount for Status="Up" and another row for Status="Down". I need to calculate something like

CalcPct = CALCULATE(SUM(EventCount),Status="Down")  / CALCULATE(SUM(EventCount))

Then I can get the percentage for each StationID, as well as the percentage for each RegionID, each CountyName and the overall percentage for all stations. The percentage by Region is displayed in a tabular visual (ideally, a New Matrix that lets me drill from RegionID to StationID), and the percentage by CountyName is displayed on a Filled Map.

If I create this as a measure, it works fine. But I can't create a slicer on a measure, so that I can let the user see the regions, stations and counties for whatever range of percentages they want.

If I create this calculation as a calculated column, I can't avoid a circular reference and so I can't get the calculation I need.

Is there a way to make this work?

Thanks again for your help.

Sam