Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
You could create a column like
Minimum column =
MINX ( { 'Table'[Price 1], 'Table'[Price 2], 'Table'[Price 3] }, [Value] )
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])
)
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.
You could create a column like
Minimum column =
MINX ( { 'Table'[Price 1], 'Table'[Price 2], 'Table'[Price 3] }, [Value] )
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.
The columns are inside the table constructor { }, so it is a table.
Oh thank you! The table constructor isn't something I've ever seen before! This seems to have worked a treat!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
66 | |
62 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |