DAX: retrieve the minimum value among the values above another

[FDANIEL]

Hello,

I need help from the community

I need to find a solution in DAX

Let me explain:

I have several columns: Item, QuantityA and QuantityB. (QuantityB is from a merge with another table in Power Query)

I would like to keep among the values of QuantityB the smallest value among those which are superior to the values of QuantityA. But if no value of QuantityB is greater than QuantityA then I have to write 0.

Example : 

Origine :

Articles	QuantitéA	QuantitéB
A	25	12
A	25	50
A	25	80
B	45	25
B	45	0
C	80	100
C	80	20

Desired résult :

Articles	QuantitéA	QuantitéB
A	25	50
B	45	0
C	80	100

I already have a solution in Power Query but the problem is that on all my data, it requires too many resources to the computer and some employees can not update the data without the computer crashes.

Thank you in advance for your help,
Good day to all,

Sincerely,

FDANIEL

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
ADDCOLUMNS (
    SUMMARIZE ( Data, Data[Articles], Data[QuantityA] ),
    "@QuantityB",
        SUMX (
            FILTER (
                GROUPBY (
                    FILTER (
                        ADDCOLUMNS ( Data, "@flag", IF ( Data[QuantityA] < Data[QuantityB], 1 ) ),
                        [@flag] = 1
                    ),
                    Data[Articles],
                    "@MinB", MINX ( CURRENTGROUP (), Data[QuantityB] )
                ),
                Data[Articles] = EARLIER ( Data[Articles] )
            ),
            [@MinB]
        ) + 0
)

[FDANIEL]

Hello,

I have applied your solution and it works perfectly.

Thank you very much for your help.

Sincerely,

FDANIEL