- There are three functions in DAX
to compute the minimum value, they are MIN, MINA and MINX.
There is a slight difference between MIN and MINA
in the way they handle Boolean values.
Whereas MIN is nothing but the short version of MINX.
MIN can be used whenever you are aggregating one column
only and that column already belongs to the model.
Let's see that with some code.
Min net price one uses MIN over sales net price.
It's only one column, that column belongs to the model,
so I can use MIN and the result is the minimum price.
You can obtain the very same result using MINX.
MINX iterates over sales and computes the minimum net price.
In this case, MIN and MINX produce the very same result.
You use MINX whenever you want to retrieve
the minimum value of an expression,
like in the third calculation where I compute the MINX
over sales of quantity times net price,
because I'm not using one column only,
but I have a more complex expression, I need to use MINX.
Now, in this case, the numbers are identical because
in this specific database, all the quantities are one,
but generally speaking MINX is useful
to aggregate expressions.
As I said, there are differences between
MIN and MINA and MINX in the way they handle Boolean value.
Neither MIN nor MINX can work on Boolean value,
you need MINA to aggregate Boolean expressions.
I have created a column no cars in the customer table
that indicates whether the customer has a car or not
and if you want to aggregate it using MIN you cannot
use neither MIN nor MINX you need to use MINA.
Keep in mind false is translated to zero
and true is translated to one.
So the MINA of customer no cars returns zero
if there is at least one customer
that has false in that column.
If you want to use MINX instead of MINA,
then you need to convert the Boolean value into an integer
and you can do that by multiplying the Boolean value by one.
One by one is still one and one by zero is zero.
So you do not lose information during the conversion
but you move the data type from a Boolean value
to an integer that can be aggregated using MINX.
When you use MIN, you can specify one argument
and in that case, the argument needs to be
a column in the model.
But MIN can also be used with two arguments
and if that is the case, the meaning of the argument
is very different.
MIN with one argument requires one column in the model.
MIN with two arguments requires two expressions,
which are evaluated in the current context
produce two numbers and MIN returns the smallest of the two.
So if you look at this code, I create first of all
sales amount that just compute sales amount,
and then a second measure 5 millions or sales amount
that returns the minimum between 5 million and sales amount.
So the second measure retrieves the sales amount
if it is smaller than 5 million or 5 millions to top it.
The result is visible here, when you have a number
which is smaller than 5 million,
5 million sales amount returns the same number.
But if the number becomes larger,
then you only obtain 5 million and no more than that.
Another scenario where you need to use MINX
and you cannot use MIN even though you're aggregating
one column only is when the column belongs to a variable,
so it is not a column in the model,
it's a column that you computed on the fly.
Let's see the example.
Here I am creating a variable monthly sales
that computes the new column monthly sales,
which is added to the calendar year month.
So I build a table that contains the calendar year month
and the monthly sales.
The monthly sales column does not belong to the model,
it's created on the fly in my measure.
I then filter out from this monthly sales table all the rows
that have monthly sales less than 10,000, and then
I want to retrieve the minimum of the values left.
I cannot use MIN in such a case,
because the column even though it is only one column
does not belong to the model.
It's a temporary column created on the fly.
That is why I need to use MINX to iterate
over the filtered sales table and retrieve the only column
that I'm interested in retrieving.
MINX is needed whenever you need to retrieve
the minimum of a column that does not belong
to a model but it is stored previously in a variable.