Is there a way to compare multiple columns in PowerPivot using DAX?

Question

Say I have a table in a PowerPivot that looks like this:

Example PowerPivot table to illustrate question

For each row, I want to find the minimum value across Columns 1, 2 and 3 and display that value in the column "MinColumn". The built-in MIN function only seems to operate on a column though, and not a row.

Is there any way to do this, other than some kind of nested IF expression? If we have a lot of columns to compare, that would get very messy, very quickly.

PowerPivot/DAX does some great column-based stuff (to be expected given it's use of xVelocity) but seems to get complex when you start looking at row-level functionality.

Is the list of possible values in the columns finite and (fairly) small? — Kyle Hale
– Kyle Hale, Commented Mar 25, 2016 at 2:56

Bill Anton · Accepted Answer · 2013-01-03 20:52:27Z

1

Another option is to push the calculation down to the source.

For example, if your source is a database table, you could create a view (or simply use a named query) and calculate the MIN (across the 3 other columns) before you pull the data into PowerPivot.

Note: the TSQL version would also be fairly ugly, PIVOT + MIN() OVER()

edited Jan 3, 2013 at 20:52

answered Jan 3, 2013 at 20:47

Bill Anton

2,99020 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GShenanigan Over a year ago

Hey, thanks for the answer. Unfortunately, a lot of the columns are defined as calculations within the data model itself, so ideally looking for a way to do it within PowerPivot. As you say though, alternative would be to move all calculations to the data source and perform the operation there.

Collectives™ on Stack Overflow

Is there a way to compare multiple columns in PowerPivot using DAX?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related