3

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.

GShenanigan
  • 5,409
  • 5
  • 38
  • 48

1 Answers1

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()

Bill Anton
  • 2,920
  • 17
  • 23
  • 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. – GShenanigan Jan 22 '13 at 09:08