Forum Discussion
mhsk
Helper II
Helper IIObtain single value from column with max values into a new column
Easy question:
how to get just a single value from sumQty_by_item column?
| location | part | qty | sum_qty_by_item | date | expected result |
| 1 | 111 | 10 | 60 | 01.05.2019 | 60 |
| 2 | 111 | 20 | 60 | 01.05.2019 | 0 |
| 3 | 111 | 30 | 60 | 01.05.2019 | 0 |
| 1 | 222 | 5 | 9 | 01.05.2019 | 9 |
| 2 | 222 | 1 | 9 | 01.05.2019 | 0 |
| 3 | 222 | 3 | 9 | 01.05.2019 | 0 |
| 1 | 111 | 5 | 25 | 02.05.2019 | 25 |
| 2 | 111 | 6 | 25 | 02.05.2019 | 0 |
| 3 | 111 | 7 | 25 | 02.05.2019 | 0 |
| 4 | 111 | 7 | 25 | 02.05.2019 | 0 |
is it even possible with dax? i need it in new column as im using it in chart/as slicer etc.
Hi mhsk
I would suggest you to add an index column in query editor.Then create the column as below:
Column = VAR pre_sum = CALCULATE ( MAX ( 'Table'[sum] ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) RETURN IF ( pre_sum <> 'Table'[sum], CALCULATE ( MAX ( 'Table'[sum] ), ALLEXCEPT ( 'Table', 'Table'[sum] ) ), 0 )Regards,
5 Replies
v-cherch-msftMicrosoft Employee
Hi mhsk
You may try below dax.
Column = IF ( [location] = 1, CALCULATE ( MAX ( [sum_qty_by_item] ), ALLEXCEPT ( 'Table', 'Table'[sum_qty_by_item] ) ), 0 )Regards,
mhskthanks for a prompt reply, the thing is I don't want to connect it anyhow to location imagine my table looks:
part sum expected result 111 50 50 111 50 0 111 50 0 222 60 60 222 60 0 222 60 0 333 1 1 333 1 0 333 1 0 444 10 10 444 10 0 444 10 0 - v-cherch-msft
Hi mhsk
I would suggest you to add an index column in query editor.Then create the column as below:
Column = VAR pre_sum = CALCULATE ( MAX ( 'Table'[sum] ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) RETURN IF ( pre_sum <> 'Table'[sum], CALCULATE ( MAX ( 'Table'[sum] ), ALLEXCEPT ( 'Table', 'Table'[sum] ) ), 0 )Regards,