Forum Discussion
DAX: retrieve the minimum value among the values above another
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
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 )
2 Replies
- Jihwan_Kim
Super User
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 )