Calculating min for each row of a table

[anettpb]

Dear Community,

I tried to create a formula which get the minimum values from 2 columns and put it to another column (Mid case):

=MIN(B2;C2)+D2

                        B                C           D

Product	Open quantity	Stock	Secured	Mid case
A	1 776	2 675	1 104	2 880
B	654	3 016	1 405	2 059
C	1 842	4 365	2 045	3 887

Does anyone has a solution for this?

Thank you,

Anett

[johnt75]

How are the 2 tables related ? 

[anettpb]

Dear @johnt75 

The relationship looks like this:

The first table contains the column which should be placed in the MIN function, the other one is in the second table, in the second column. So those columns which should be placed in the MIN function are contains whole number values. The common point between the 2 tables is the hybrid names.

[johnt75]

The code you posted using RELATED should work if you are adding the column to the 'Rendelési adatok' table. Use the data view to sort that table first by Open Qty and then by Secured, see if you have negative values in there.

[anettpb]

Dear @johnt75 

And there is any other solution which calculating MIN between 2 value from 2 different table using their relationship?

Or I must add somehow the 2 value in 1 column and it is not enough that they have relationship and in the visual they are in the same table?

[johnt75]

Is [Secured] a measure or a column? If it is a column, which table is it in? If it is a measure, what is the definition ?

[anettpb]

Hi @johnt75 

I have 2 table, 1 contains the open qty and another 1 is the stock. I have to "compare" this 2 value and get the min of them, then add to it my secured value, which is a measure (sum of 2 other value).

[johnt75]

Can you post the DAX that is giving the error

[anettpb]

Hi @johnt75 

Sure, this is what I used:

Mid case = MIN('Rendelési adatok'[Nyitott mennyiség (ESU)],RELATED(pivot[Sum of stock]))

(Nyitott mennyiség (ESU) this what I shared with you as [Open qty])

[johnt75]

You added that as a column on the 'Rendelési adatok' table, right? Not as a measure? Because that looks to me like it should work

[anettpb]

HI @johnt75 

I checked it again and it worked well, sorry.

But if I would like to add to it the Secured values (Secured is a measure in another table), the values became high negative numbers.

[johnt75]

OK, something from 'Rendelési adatok' is probably filtering the tables involved in the [Secured] measure, either directly. Because you have a row context on 'Rendelési adatok' then every column in the expanded table is being added to the filter context when you compute the measure. You probably want to restrict the columns using ALLEXCEPT.

[anettpb]

Hi @johnt75 

Thanks for your continuous help. I used finally an extra step, and not directly added the Secured values to the MIN ones, and it worked well 🙂

[johnt75 · Accepted solution]

Just for debugging, create one column which just pulls the min of the 2 values,

Mid case = MIN('Rendelési adatok'[Open qty],RELATED(pivot[Sum of stock])),

and then create another column which is just 

Secured stock = [Secured]

That should help to pinpoint where the problem lies, either in the MIN or in the measure.

[anettpb]

HI @johnt75 

I tried it and I got this error message:

[anettpb]

Dear @johnt75 

Thank you for the tip, but it is not working. I have to use 2 different source, so I tried this one 

Mid case = MIN('Rendelési adatok'[Open qty],RELATED(pivot[Sum of stock]))+[Secured]

But it is ot worked, I've got a lot of big minus qtys, so it seems that this function is not working as in Excel? Or my datasource is not proper?

[johnt75]

You can create a calculated column like

Mid case =
MIN ( 'Table'[Open quantity], 'Table'[Stock Secured] )