Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rob_B
Helper I
Helper I

Filter visualizations by measure value AND column value

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:

 

SectorActivityYearBenefitsTRC Cost
AAA2020010
AAA2021020
BBA2022030
BBA2023040
CCB20202010
CCB20213030
DDB20224060
DDB202350100
AAC20203015
AAC20214040
BBC20225070
BBC202360100

 

1 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
)
)

 

View solution in original post

2 REPLIES 2
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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
)
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.