Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
anettpb
Helper I
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

ProductOpen quantity StockSecuredMid case 
A1 7762 6751 1042 880
B6543 0161 4052 059
C1 8424 3652 0453 887

 

Does anyone has a solution for this?

 

Thank you,

Anett

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

View solution in original post

16 REPLIES 16
johnt75
Super User
Super User

How are the 2 tables related ? 

Dear @johnt75 

 

The relationship looks like this:

anettpb_0-1678289148320.png

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.

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.

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?

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 ?

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

 

Can you post the DAX that is giving the error

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

anettpb_0-1678719822365.png

 

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

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.

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.

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 🙂

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.

HI @johnt75 

 

I tried it and I got this error message:

anettpb_0-1678711039176.png

 

anettpb
Helper I
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?
johnt75
Super User
Super User

You can create a calculated column like

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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