cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculating min for each row of a table

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

1 ACCEPTED SOLUTION
Super User

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.

16 REPLIES 16
Super User

How are the 2 tables related ?

Helper I

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.

Super User

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.

Helper I

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?

Super User

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 ?

Helper I

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

Super User

Can you post the DAX that is giving the error

Helper I

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

Super User

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

Helper I

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.

Super User

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.

Helper I

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 🙂

Super User

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.

Helper I

HI @johnt75

I tried it and I got this error message:

Helper I

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?
Super User

You can create a calculated column like

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors