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
MidiGlitch
Frequent Visitor

Filter data by excluding max and min values

Hi All:

I have a table grouped by Ids and for each Id there are several rows. Unfortunately, both the max and min values of a column I wish to display are spurious and I want to filter them out of the aggregates.

 

i.e. something like the following psuedo code:

Average all values in col X that are not Min[X] nor Max[X] Group By ID

 any thoughts?

 

thx

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @MidiGlitch,

 

Add this measure to your data:

Average_Without_Max_MIN =
VAR Maximum =
    MAX ( Table2[Values] )
VAR Minimum =
    MIN ( Table2[Values] )
RETURN
    CALCULATE (
        AVERAGE ( Table2[Values] ),
        Table2[Values] <> Maximum
            && Table2[Values] <> Minimum
    )

As you can see below it ignores the max and minimum in each group

 

Max_min.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @MidiGlitch,

 

Add this measure to your data:

Average_Without_Max_MIN =
VAR Maximum =
    MAX ( Table2[Values] )
VAR Minimum =
    MIN ( Table2[Values] )
RETURN
    CALCULATE (
        AVERAGE ( Table2[Values] ),
        Table2[Values] <> Maximum
            && Table2[Values] <> Minimum
    )

As you can see below it ignores the max and minimum in each group

 

Max_min.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Excellent, except...

 

After allowing DAX expressions in Options, I am just getting back zeros

Hi @MidiGlitch,

 

How is your visuals set-up? Did you do a Measure?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.