cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Max of four values in a measure

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?

2 REPLIES 2
Frequent Visitor

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?

Impactful Individual

@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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors