Min/Max of a list of variables

[daveedd]

Hi all,

QQ.  I have a bunch of calculations which end in a list of variables sat with some values, eg

var madeupvar1 = 4

var madeupvar2 = 30

var madeupvar3 = 35

var madeupvar4 = 100

var madeupvar5 = 32

(of course there's a calculation to get to the value, but that's not important to the question)

All I'm looking to do is drop off the top and bottom values as outliers to make some averaging more reliable.  I can't use max or min as they're not in a column.  I put them into a single var and can use topn to get the largest, but I cannot get the smallest value.

Any ideas?

[OwenAuger · Accepted solution]

Hi @daveedd 

There are probably a few ways you could do this.

They would all involve constructing a single-column table containing the variable values.

Here are a couple of options (using either MINX/MAXX or TOPN), with VarListTrimmed containing the reduced list of values, to use as required:

VAR madeupvar1 = 4
VAR madeupvar2 = 30
VAR madeupvar3 = 35
VAR madeupvar4 = 100
VAR madeupvar5 = 32
VAR VarList = { madeupvar1, madeupvar2, madeupvar3, madeupvar4, madeupvar5 }
VAR VarMin =
    MINX ( VarList, [Value] )
VAR VarMax =
    MAXX ( VarList, [Value] )
VAR ValuesToRemove = { VarMin, VarMax }
VAR VarListTrimmed =
    EXCEPT ( VarList, ValuesToRemove )

VAR madeupvar1 = 4
VAR madeupvar2 = 30
VAR madeupvar3 = 35
VAR madeupvar4 = 100
VAR madeupvar5 = 32
VAR VarList = { madeupvar1, madeupvar2, madeupvar3, madeupvar4, madeupvar5 }
VAR VarMin =
    TOPN ( 1, VarList, [Value], ASC )
VAR VarMax =
    TOPN ( 1, VarList, [Value], DESC )
VAR ValuesToRemove =
    UNION ( VarMin, VarMax )
VAR VarListTrimmed =
    EXCEPT ( VarList, ValuesToRemove )

Regards,

Owen

[Marcinvelcino]

HI ,

I have one question, what exactly is " [Value] " part of this code ? When you refer to a list of measures you put this [Value] . I didn't find any info about this in dax.guide. 

[OwenAuger]

Hi @Marcinvelcino 

When a table is created with the table constructor, the columns are named automatically.

Single column: Value

Multiple columns: Value1, Value2, ...

Example:

https://dax.do/XDfV48Dah5bBNo/

[daveedd]

Awesome thanks a lot!