Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
)
)
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
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
)
)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |