cancel
Showing results for
Did you mean:
Helper I

## Add a new column showing the lowest value across a row

I'm trying to use DAX to add a new column that shows the lowest value on a row across multiple columns. As an example, with this data:

 Item Price 1 Price 2 Price 3 A1 £97.00 £92.00 A2 £95.00 £98.75 A3 £110.00 £110.50 A4 £95.00 £98.00 £95.00 A5 £9.00 £8.50 A6 £9.00 £10.50 £9.15 A7 £155.00 £75.00 £89.99 A8 £103.75 £114.80 A9 £22.33 £25.00 A10 £250.00 £225.75 £230.00

the new column would look something like this:

 Item Price 1 Price 2 Price 3 Minimum A1 £97.00 £92.00 £92.00 A2 £95.00 £98.75 £95.00 A3 £110.00 £110.50 £110.00 A4 £95.00 £98.00 £95.00 £95.00 A5 £9.00 £8.50 £8.50 A6 £9.00 £10.50 £9.15 £9.00 A7 £155.00 £75.00 £89.99 £75.00 A8 £103.75 £114.80 £103.75 A9 £22.33 £25.00 £22.33 A10 £250.00 £225.75 £230.00 £225.75

I can't for the life of me work out how to do it, so would be very grateful for any help!

1 ACCEPTED SOLUTION
Super User

You could create a column like

Minimum column =
MINX ( { 'Table'[Price 1], 'Table'[Price 2], 'Table'[Price 3] }, [Value] )
6 REPLIES 6
Frequent Visitor

Minimum = MINX(
FILTER(
'YourTableName',
NOT(ISBLANK('YourTableName'[Price 1])) ||
NOT(ISBLANK('YourTableName'[Price 2])) ||
NOT(ISBLANK('YourTableName'[Price 3]))
),
MIN('YourTableName'[Price 1], 'YourTableName'[Price 2], 'YourTableName'[Price 3])
)

Helper I

Thanks for responding! I think the issue with this is that MIN has a maximum argument count of 2, so the MIN('YourTableName'[Price 1], 'YourTableName'[Price 2], 'YourTableName'[Price 3]) part throws up an error.

Super User

You could create a column like

Minimum column =
MINX ( { 'Table'[Price 1], 'Table'[Price 2], 'Table'[Price 3] }, [Value] )
Helper I

Thanks for replying, I don't think this one works fror the problem because MINX wants you to return a table in the first argument, not a column.

Super User

The columns are inside the table constructor { }, so it is a table.

Helper I

Oh thank you! The table constructor isn't something I've ever seen before! This seems to have worked a treat!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors