There are 20 columns in the table and I need the Column Name and value of the MAX date to be represent in a two columns, 1 Column called “Stage Name” and the Other with “Stage Date”. If I unpivot the columns named Stage 1 Date to Stage 7 Date, I will end up with multiple rows containing the other 13 colums where there was multiple dates is the columns Stage 1 to Stage 7 Date. I only require one row.
In the example table I provided, if I unpivot, I will end up with 3 rows.
Row #; Stage Name; Stage Date; Other Columns in Table.
9;Stage 3 Date;03/10/2016; Other Columns from same Row in Table
9;Stage 4 Date;28/06/2016; Other Columns from same Row in Table
9;Stage 5 Date;19/10/2016; Other Columns from same Row in Table
In the above example, I only wish two Keep the first Row as that’s the one with the MAX date and I also have the Stage name (Stage 3) that contained the Max Date.
In the other columns I have as Total Value for that row, so if I end up with three rows, then the total value will be tripled. I want to avoid have to filter all the rows in the DAX Measures just to include the one row with the MAX date, which is why I just was to delete all the other rows from the Table.
Maybe this is not as simple as I thought it would be?