Filter visualizations by measure value AND column value

[Rob_B]

I need to filter my visualizations by a measure value and a column value. Visual filters appear to only allow filtering the measure by the measure value. In any case, this approach would not be ideal since it would have to be applied to each and every visual.

The measure that requires filtering ([Scenario TRC Ratio]) uses the DIVIDE function on two other measures that include what-if parameters. Only the results where Forecast[Activity]<>"B" AND [Scenario TRC Ratio] < 1.0  should be excluded, where:

Scenario TRC Ratio = DIVIDE([Scenario Benefits]|[Scenario TRC Cost]|0)

Scenario Benefits = SUM(Forecast[Benefits]) * (1+[Parameter1])

Scenario TRC Cost = CALCULATE(
SUMX(Forecast|IF(Forecast[Sector]="AA"|Forecast[TRC Cost]+DIVIDE(MAX(Parameter2[Parameter2])|POWER(1.0766|2))|Forecast[TRC Cost])
)
)

and the Forecast table looks something like this:

Sector	Activity	Year	Benefits	TRC Cost
AA	A	2020	0	10
AA	A	2021	0	20
BB	A	2022	0	30
BB	A	2023	0	40
CC	B	2020	20	10
CC	B	2021	30	30
DD	B	2022	40	60
DD	B	2023	50	100
AA	C	2020	30	15
AA	C	2021	40	40
BB	C	2022	50	70
BB	C	2023	60	100

[Pragati11]

Hi @Rob_B ,

To filter all the visualisations you need to have a slicer which can only be a column. You can never use a Measure in a slicer.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Rob_B · Accepted solution]

Thanks @Pragati11, that's good to know!

I was able to achieve the result I wanted by creating a new measure using the CALCULATE and FILTER functions with logical operators:

Scenario TRC Ratio (adjusted) = CALCULATE(
'Scenario Measures'[Scenario TRC Ratio]|Filter(Forecast|Forecast[Activity]<>"B" || Forecast[Activity]="B" && 'Scenario Measures'[Scenario TRC Ratio]>=1
)
)