Have you ever needed to calculate the max value of multiple measures or multiple columns? If you do some googling, you might find solutions talking about nested if statements. Without even going into how you would do this, let me save you some time and explain the “proper” solution. What we’re going to do instead is to create a single-column table and then take the max of the column. For example, if I have 3 columns and a measure titled “Revenue”, “LY Revenue”, “Budget”, [Measure1], respectively, then the code to get the maximum value of the four options is as follows:

Max Value =
VAR ValueTable =
UNION(
   ROW(“Value”,SUM(Data[Revenue])),
   ROW(“Value”,SUM(Data[Revenue])),
   ROW(“Value”,SUM(Data[Revenue])),
   ROW(“Value”,[Measure1])
)
RETURN
MAXX(ValueTable,[Value])

Explanation

Since aggregation functions (like MAX, MIN, SUM, etc.) only work over columns, it is impossible to say something like MAX(Column1,Column2). Because of this fact, we want to put all of our values into a single column and then use the MAXX function. The UNION function basically pastes together our ROW values, providing us with a nice single-column table to perform our aggregation 😊

%d