Max of four values in a measure

JrBIAnalyst — Fri, 14 Jul 2023 15:42:33 GMT

This falls under the heading of "It works, but surely there's a better way to do this."

Background: I have a measure using LINEST to project results based on current data. I want to calculate and display the upper and lower bound of the LINEST as error bars. You can interpret the results of LINEST as y = (Slope1 +/- StandardErrorSlope1) * x + (Intercept +/- StandardErrorIntercept) which provides a range of values bounding the result of the LINEST expression. That means there are four possible expressions (++, +-, -+, and --) that could return the bounding values of y.

In DAX terms, that looks like this.

CPI-U Linest UB = VAR line = LINESTX ( CalculateTable(CPI, AND(CPI[Month] >= SELECTEDVALUE('Reference Month'[Month]), CPI[DateType] = "current")), CPI[CPI-U], CPI[Month] ) VAR m = SELECTCOLUMNS ( line, [Slope1] ) VAR em = SELECTCOLUMNS ( line, [StandardErrorSlope1] ) VAR b = SELECTCOLUMNS ( line, [Intercept] ) VAR eb = SELECTCOLUMNS ( line, [StandardErrorIntercept] ) VAR x = SELECTEDVALUE ( CPI[Month] ) VAR ypp = (m + em) * x + (b + eb) VAR ypm = (m + em) * x + (b - eb) VAR ymp = (m - em) * x + (b + eb) VAR ymm = (m - em) * x + (b - eb) RETURN Max(Max(Max(ypp,ypm),ymp),ymm)

My question relates to that last line, RETURN Max(Max(Max(ypp,ypm),ymp),ymm). Surely, there's a better way to return the Max of four variables... right?

Re: Max of four values in a measure

BeaBF — Fri, 14 Jul 2023 18:00:58 GMT

JrBIAnalyst Hi!
In my opinion, instead of nesting multiple MAX functions, you can use the MAXX function in combination with VALUES to iterate over the four variables and find the maximum value:
RETURN MAXX(VALUES("TempTable"[TempColumn]), [Variable])

BBF

Re: Max of four values in a measure

JrBIAnalyst — Wed, 19 Jul 2023 15:56:19 GMT

Thanks, BeaBF.

I had looked at something like that and concluded that constructing the tempTable felt more complicated than nesting maxes. How would you go about creating that tempTable?

topic Re: Max of four values in a measure in DAX Commands and Tips

Max of four values in a measure

Re: Max of four values in a measure

Re: Max of four values in a measure